SELECT all items in common between two users on three tables
I have three tables
item_to_user (to store the relations between user and item)
| item_to_user_id | user_id | item_id |
-----------------------------------------
item_tb
| item_id | item_name |
-----------------------
user_tb
| user_id | user_name |
-----------------------
An item can belong to one or more user and viceversa, that's why I'm using the first table.
So, given the user_id = A and user_id = B how can I do a mysql query to select all the 开发者_StackOverflowitems the belong both to user A and user B?
note: I wrote a similar question yesterday but was about two tables not three.
SELECT i.*
FROM item_tb AS i
LEFT JOIN item_to_user AS iu
ON iu.item_id = i.item_id
LEFT JOIN user_tb AS u
ON iu.user_id = u.user_id
WHERE u.user_name IN ('A', 'B')
GROUP BY i.item_id
HAVING COUNT(i.item_id) > 1
By prequerying common items between A and B (via count(*) = 2) will pre-limit the final list of items possible to get details from. Then joining that to the items table as SECOND table in the query should help performance. Especially if A&B have 50 common items, but your items table consists of 1000's of items.
select straight_join
i.item_id,
i.item_name
from
( select iu.item_id
from item_to_user iu
join user_tb u
on iu.user_id = u.user_id
and u.user_name in ( 'A', 'B' )
group by 1
having count(*) = 2 ) Matches,
item_tb i
where
Matches.item_id = i.item_id
If a user can't have repeated items then this simple one will work:
select item_id
from item_to_user
where user_id in ('A', 'B')
group by item_id
having count(*) > 1
精彩评论