It is a sales order info table, and users order different products.

I need to look up the users have order all three products (01,03,05)

I know I can easily user

where product in

to find users ordered one of the products, but I am stuck on how to look up for users ordered all three (and of course the products are more than just those three).

Help please, thank you.

CodePudding user response：

You can try like following using count distinct

select UserId,count(distinct productid )
from TABLENAME
where productid in('01','02','03')
group by UserId
having count(distinct productid )=3