MySQL Selecting Linked Rows

Question

I have two tables:

Table "A":

+----------+
| item_id  |
+----------+
| 10       |
| 20       |
| 30       |
| 40       |
+----------+

and table "B":

+----------+-------------+
| item_id  |   user_id   |
+----------+-------------+
| 10       | 1           |
| 10       | 2           |
| 20       | 1           |
| 30       | 2           |
+----------+-------------+

the field "item_id" is a common field.

If i wish to select all rows from table A where both users 1 and 2 exist (but not the rows where only one of the two exists), how might I got about building this query? (I'm looking to have one row in the result where item_id is 10).

Answer 1

Try this:

  SELECT a.item_id
    FROM TABLE_A a
    JOIN TABLE_B b ON b.item_id = a.item_id
   WHERE b.user_id IN (1, 2)
GROUP BY a.item_id
  HAVING COUNT(*) = 2

Answer 2

select item_id from table_b where user_id = 1
intersect
select item_id from table_b where user_id = 2

Answer 3

Another alternative:

SELECT A.*
FROM tableA A
INNER JOIN tableB B1 ON B1.item_id = A.item_id AND B1.user_id = 1
INNER JOIN tableB B2 ON B2.item_id = A.item_id AND B2.user_id = 2

Joins A to B twice, once for one user, again for the other user. It's another way of stating a self-join from B to itself to find the items with both users.

This would not extend well beyond two users (each one requires an additional join).

Answer 4

SELECT a.item_id FROM a
    INNER JOIN b x ON a.item_id = x.item_id
    INNER JOIN b y ON x.item_id = y.item_id   
        AND x.user_id = 1 AND y.user_id = 2;