mysql intersection, comparison, opposite of UNION?

I'm trying to compare two set of resutls aving hard time to undesrtand how subqueries work and if they are efficient. I'm not gonna explain all my tables, but just think i have apair of arrays...i might do it in php but i wonder if i can do it in mysql right away...

开发者_运维百科

this is my query to check how many items user 1 has in lists he owns

SELECT DISTINCT *
FROM list_tb 
INNER JOIN item_to_list_tb 
ON list_tb.list_id = item_to_list_tb.list_id
WHERE list_tb.user_id = 1
ORDER BY item_to_list_tb.item_id DESC 

this is my query to check how many items user 2 has in lists he owns

SELECT DISTINCT *
FROM list_tb 
INNER JOIN item_to_list_tb 
ON list_tb.list_id = item_to_list_tb.list_id
WHERE list_tb.user_id = 1
ORDER BY item_to_list_tb.item_id DESC 

now the problem is that i would intersect those results to check how many item_id they have in common...

thanks!!!

---

Unfortunately, MySQL does not support the Intersect predicate. However, one way to accomplish that goal would be to exclude List_Tb.UserId from your Select and Group By and then count by distinct User_Id:

Select ... -- everything except List_Tb.UserId
From List_Tb 
    Inner Join Item_To_List_Tb 
        On List_Tb.List_Id = Item_To_List_Tb.List_Id
Where List_Tb.User_Id In(1,2)
Group By ... -- everything except List_Tb.UserId
Having Count( Distinct List_Tb.User_Id ) = 2
Order By item_to_list_tb.item_id Desc

Obviously you would replace the ellipses with the actual columns you want to return and on which you wish to group.