MySQL query matching multiple fields?

Question

I'm confused on how to write the following query in MySQL: Given one collaborator, I'd like to get all the collaborators that participated on each item that this collaborator worked on.

Here's my collaborators table:

id collaborator_id item_id
1      1              1
2      2              1
3      3              1
4      4              2
5      1              2
6      2              3

for collaborator_id=1, the query would return:

collaborator_id    item_id
       1              1
       2              1
       3              1
       1              2
       4              2

So collaborator_id=1 worked with collaborator_ids=2,3 on item_id=1 and worked by themselves on item_id=2.

This seems super simple but I'm having a brain freeze on how to get these results. Thoughts?

davesnitty · Accepted Answer · 2012-10-01 01:31:16Z

5

This query joins on the item_id and gets you all the unique collaborators who worked with a given collaborator on shared items, other than the collaborator himself:

SELECT distinct b.collaborator_id, b.item_id
FROM collab_table a
    JOIN collab_table b
    on a.item_id = b.item_id
WHERE a.collaborator_id = 1
    and b.collaborator_id != 1

edited Oct 1, 2012 at 1:31

answered Oct 1, 2012 at 1:22

davesnitty

1,87016 silver badges11 bronze badges

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

4 Comments

tim peterson Over a year ago

-@davesnitty, how do i return item_id too?

davesnitty Over a year ago

@timpeterson just edited the query to do that. You'll now potentially have multiple rows per collaborator, assuming a collaborator collaborated on >1 item.

tim peterson Over a year ago

hmm, your query's not quite right. It's returning this: i.imgur.com/W9aOY.png

davesnitty Over a year ago

@timpeterson That looks right to me if I understand your question correctly. According to your data, collaborator #1 worked on items 1 and 2. He worked on item 1 with collabs. #2 and #3. He worked on item 2 with collab. #4. The result screenshot shows that.

John Woo · Accepted Answer · 2012-10-01 02:36:57Z

3

you can use subquery

SELECT *
FROM collaborator
WHERE item_ID IN
(
   SELECT item_ID
   FROM collaborator
   WHERE collaborator_id = 1
) 
ORDER BY item_ID, Collaborator_ID

or by using JOIN

SELECT a.*
FROM   COLLABORATOR a
       JOIN COLLABORATOR b
          ON a.ITEM_ID = b.ITEM_ID
WHERE  b.COLLABORATOR_ID = 1
ORDER BY item_ID, Collaborator_ID;

SQLFiddle Demo

edited Oct 1, 2012 at 2:36

answered Oct 1, 2012 at 1:45

John Woo

265k70 gold badges509 silver badges500 bronze badges

6 Comments

tim peterson Over a year ago

-@JohnWoo, i just ran @davesnitty's code vs. your code and his code takes on average 0.6ms whereas your's takes 2ms. Do you see your code ever being faster than Dave's (like on a huge, million row collaborators table)? I accepted your answer b/c it made more sense to me, but obviously i'd like to pick the most performant code. Thoughts?

John Woo Over a year ago

choose that best fits your answer always :D

davesnitty Over a year ago

My guess is that it's the join vs. the subquery and that as the dataset scales, joining on an indexed field would probably be the faster approach. However, there are probably plenty of things that could affect the performance (indexing, config settings, etc.).

tim peterson Over a year ago

@davesnitty, i still don't really understand indexing. Is the JOIN you wrote "on an indexed field"? Just to be clear on my collaborators table, id is the primary key, auto-incremented field. I haven't assigned any other keys mainly cause I don't understand them.

davesnitty Over a year ago

@timpeterson Indexing tells the DB to create a data structure that enables faster lookups on a particular field (much like an index in a paper book allows you to look up a particular term without having to search page by page). If you know you will be frequently using a field for joins, it may be good to create an index on it. For instance "CREATE INDEX ind_itemid on collab_table(item_id)" would do this on your table. dev.mysql.com/doc/refman/5.0/en/create-index.html

|

Darius Makaitis · Accepted Answer · 2012-10-01 01:25:26Z

2

What you need to do is join the table back to itself, using your filter on one of the aliases, and pulling the result set from the other:

select a.COLLABORATOR_ID, a.ITEM_ID
from COLLABORATOR as a
inner join COLLABORATOR as b on
    a.ITEM_ID = b.ITEM_ID
where b.COLLABORATOR_ID = 1

answered Oct 1, 2012 at 1:25

Darius Makaitis

8901 gold badge5 silver badges5 bronze badges

Collectives™ on Stack Overflow

MySQL query matching multiple fields?

3 Answers 3

4 Comments

SQLFiddle Demo

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related