CODEWITHSUNDEEP
sqlpostgresql
Blackboy
Blackboy

Reputation: 192

SQL select all items where team_id is ( 1 and 2 and 3 and 4 and .. rest values from list)

I try to select items where team id = list of values; For example : list of values is (9,7,6,4,3,1) and I want to create query select with item who have team_id = (9,7,6,4,3,1);

My try : 

select item_id
from t_item as i join
     team as t 
     on t.item_id = i.id
where t.id in (1221, 1219, 1);

But it working likes in (1221 or 1219 or 1) but i want in(1221 and 1219 and 1)

Upvotes: 1

Views: 501

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

I think you want group by and having as well:

select i.item_id
from t_item as i join
     team as t 
     on t.item_id = i.id
where t.id in (1221, 1219, 1)
group by i.item_id
having count(distinct t.id) = 3;

Note that you do not need the join:

select t.item_id
from team t 
where t.id in (1221, 1219, 1)
group by t.item_id
having count(distinct t.id) = 3;

And if the rows in team are unique, use count(*) rather than count(distinct).

Upvotes: 4

Related Questions