Creating an SQL statement to return the identifiers of users who buy multiple items

Question

I am sure the solution is simple, but it is shying away from me.

The table is configured as follows:

TABLE NAME = sales Field 1 = id INT Auto Increment Primary Key Field 2 = user_id INT NOT NULL Field 3 = item VARCHAR(100) NOT NULL 

So, let's say I'm looking for users who have purchased the following items:

• bid
• footwear
• bike

How would I structure the SQL statement to return the user_id values ​​of the users who purchased each of the three elements (the user had to buy all three elements)?

Answer 1

Match table views for each item

It is required to distinguish, since the user can buy a lot from one element.

 select distinct(ipod.user_id ) from sales ipod inner join sales shoes on shoes.user_id = ipod.user_id inner join sales bike on bike.user_id = ipod.user_id where ipod.item = 'ipod' and shoes.item = 'shoes' and bike.item = 'bicycle' 

Answer 2

This method also facilitates access to other elements:

  select USER_ID, count(distinct ITEM) from SALES where ITEM in ('ipod', 'shoes', 'bicycle') group by USER_ID having count(distinct ITEM) = 3 

Answer 3

The simplest solution would be the following.

 select user_id from sales s1 where exists (select user_id from sales s2 where item = 'ipod' and s2.user_id = s1.user_id) and exists (select user_id from sales s2 where item = 'shoes' and s2.user_id = s1.user_id) and exists (select user_id from sales s2 where item = 'bycycle' and s2.user_id = s1.user_id)