Query to get all companies that have products with all specified attributes

Let’s say I have two tables (1 to many): table company with fk id and table product with fk id and also fields sId and tId (if sId has data tId is null and vice versa, not sure if it matters).

I want every company that has products with, for example sId=1, sId=2, tId=3 and tId=4.

So for a company to be eligible it has to have products with sId=1 and sId=2 and tId=3 and tId=4. If one is missing it shouldnt appear.

I tried joining the tables and doing

where pro.sId in ('1', '2') 
and pro.tId in ('3','4')

But it doesn’t give me any companies. Any help would be appreciated.

Answer

There are many ways to achieve this. One is:

select * from company where id in
(
  select company_id from product where sid = 1
  intersect
  select company_id from product where sid = 2
  intersect
  select company_id from product where tid = 3
  intersect
  select company_id from product where tid = 4
);

Another:

select * from company
where id in (select company_id from product where sid = 1)
  and id in (select company_id from product where sid = 2)
  and id in (select company_id from product where tid = 3)
  and id in (select company_id from product where tid = 4);