Consider I have 2 tables One User

and the other Items

Is there a way to get a list of all users who have all the items in a given list (say ["item1","item2"]) i.e in this case only user - 123 is a valid response. the below would be the expected response in this case

Right now I am thinking of pulling in data using in ("item1","item2") and then processing it in memory. I am looking for a way to accomplish this using a query.

CodePudding user response：

Try:

select u.userId,u.userName
from Users u 
inner join ( select userId
             from Items
             where itemName in ('item1','item2')
             group by userId
             having count(itemName)=2
            ) as i  on u.userId=i.userId;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/168

Subquery returns only the userId from Items table which have both itemName.

CodePudding user response：

select u.* from User u
  join (select userId from Items where itemName='item1' 
  intersect
  select userId from Items where itemName='item2') sub 
on u.userId=sub.userId;

CodePudding user response：

Using an EXISTS with a HAVING will get you that.

SELECT *
FROM Users AS usr
WHERE EXISTS (
  SELECT 1
  FROM Items AS itm
  WHERE itm.userId = usr.userId
    AND itm.itemName IN ('item1', 'item2')
  HAVING COUNT(DISTINCT itm.itemName) = 2
);

db<>fiddle here

CodePudding user response：

select *.u from
user u 
inner join (select * from item where item ='item1') a on a.id=u.id --they have item1
inner join (select * from item where item ='item2') b on b.id=a.id --they have item2
;

It takes only user that have both, sql fiddle

After your explanation

select count(*), u.id from
user u 
inner join (select * from item where item in ('item1','item2')) a on  a.id=u.id
having count(*)>1

CodePudding user response：

a with clause and array comparison:(postgres)

with base_data as (
select 
userId,
array_agg(itemName) over (partition by userId) as liste
from Items
)
select 
* from base_data
where liste @> array['item1','item2']::varchar[]