How to compare comma separated values and with two different ids-php mysql

Question

The pictures of person shown are just fake data. This is my table structure

id | Author | Co-authors

Now I want to select two authors say ronnie and baron, so now their co-authors should be compared. In this case the matches would be gopal, arun so the output should gopal and arun.

How to do that in mysql query. Please help.

You have a lousy table structure. You should have a table structure with one row per author and the work they have authored. Then your query would be much simpler to express. — Gordon Linoff
– Gordon Linoff, Commented Sep 19, 2013 at 2:55
Yah the co authors also have separate rows with different ids — ronnie
– ronnie, Commented Sep 19, 2013 at 2:57
. . Then show that table structure. Also, choose which database you are really using. — Gordon Linoff
– Gordon Linoff, Commented Sep 19, 2013 at 3:00
So if you look at the picture, there are two co-authors matches one is sreeram and one is frank kruzel , so only those two authors must be shown. — ronnie
– ronnie, Commented Sep 19, 2013 at 3:08

Fabien TheSolution · Accepted Answer · 2013-09-19 04:16:01Z

2

This is a suggested structure for your project. May be it will be an articles table in your case instead of books or anything else that authors and co-authors are related to. By this way, it will be much easier to do query on your data after :

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE books
    (`id` int, `title` varchar(5));

INSERT INTO books
    (`id`, `title`)
VALUES
    (1, 'book1'),
    (2, 'book2'),
    (3, 'book3'),
    (4, 'book4');

CREATE TABLE authors
    (`id` int, `name` varchar(8));

INSERT INTO authors
    (`id`, `name`)
VALUES
    (1, 'Ronnie'),
    (2, 'Razor'),
    (3, 'Taz'),
    (4, 'Baron'),
    (5, 'vinay'),
    (6, 'karthick'),
    (7, 'earl'),
    (8, 'gopal'),
    (9, 'webster'),
    (10, 'karthick'),
    (11, 'arun'),
    (12, 'ming'),
    (13, 'sanjeev');

CREATE TABLE type
    (`id` int, `type` varchar(9));

INSERT INTO type
    (`id`, `type`)
VALUES
    (1, 'author'),
    (2, 'co-author');

CREATE TABLE wrote
    (`id` int, `book_id` int, `author_id` int, `type_id` int);

INSERT INTO wrote
    (`id`, `book_id`, `author_id`, `type_id`)
VALUES
    (1, 1, 1, 1),
    (2, 2, 2, 1),
    (3, 3, 3, 1),
    (4, 4, 4, 1),
    (5, 1, 5, 2),
    (6, 2, 6, 2),
    (7, 3, 7, 2),
    (8, 4, 8, 2),
    (9, 1, 8, 2),
    (10, 2, 9, 2),
    (11, 3, 10, 2),
    (12, 4, 11, 2),
    (13, 1, 11, 2),
    (14, 2, 12, 2),
    (15, 3, 5, 2),
    (16, 4, 13, 2);

This is your actual data :

Query 1:

SELECT b.id, a.name as author, group_concat(co.name) as co_authors
FROM books b
INNER JOIN wrote wa ON wa.book_id = b.id AND wa.type_id = 1
INNER JOIN authors a ON wa.author_id = a.id 
INNER JOIN wrote wco ON wco.book_id = b.id AND wco.type_id = 2
INNER JOIN authors co ON wco.author_id = co.id
GROUP BY b.title, a.name

Results:

| ID | AUTHOR |            CO_AUTHORS |
|----|--------|-----------------------|
|  1 | Ronnie |      arun,vinay,gopal |
|  2 |  Razor | webster,ming,karthick |
|  3 |    Taz |   earl,karthick,vinay |
|  4 |  Baron |    sanjeev,gopal,arun |

This is what you are trying to do.

Note AND a.id in (1,4); where id 1 is Ronnie and id 4 is Baron :

Query 2:

SELECT co.name as coauthor
FROM books b
INNER JOIN wrote wa ON wa.book_id = b.id AND wa.type_id = 1
INNER JOIN authors a ON wa.author_id = a.id AND a.id in (1,4)
INNER JOIN wrote wco ON wco.book_id = b.id AND wco.type_id = 2
INNER JOIN authors co ON wco.author_id = co.id 
GROUP BY co.name
HAVING count(co.name) > 1

Results:

| COAUTHOR |
|----------|
|     arun |
|    gopal |

edited Sep 19, 2013 at 4:16

answered Sep 19, 2013 at 3:16

Fabien TheSolution

5,0501 gold badge20 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ronnie Over a year ago

That is very nice bro, but im using php too, now I am totally confused.

ronnie Over a year ago

I am confused with the book table because I don't have that thing and all.

ronnie Over a year ago

They have different subject areas

Fabien TheSolution Over a year ago

So instead of books, you will have a subject_areas table

ronnie Over a year ago

Yes bro but the layout is somewhat different

|

cstef · Accepted Answer · 2013-09-19 03:16:57Z

0

Try something like this:

A table called Authors (put all the authors and co-authors in this table)

id  |  Author
1   |  Bob
2   |  Jim
3   |  Sally
4   |  Joan

Then a table of the relations between authors

id  |  authorid  |  coauthorid
1   |  1         |  4
2   |  2         |  3
3   |  1         |  3

and so on. One row for each author/coauthor relationship.

answered Sep 19, 2013 at 3:16

cstef

741 silver badge8 bronze badges

2 Comments

ronnie Over a year ago

Could you me a mysql query suggestion for my question

cstef Over a year ago

You would just do a join to get the authors ids from the author table from the coauthorid. So you would get all of the coauthorids associated with an author, and use that id to get the name from the author table.

Collectives™ on Stack Overflow

How to compare comma separated values and with two different ids-php mysql

2 Answers 2

8 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related