假设我有两个表(1到多个(:带有fk id的表company和带有fk id的表product,还有字段sId和tId(如果sId有数据,则tId为null,反之亦然,不确定是否重要(。
我希望每一家公司都有这样的产品,例如sId=1,sId=2,tId=3和tId=4。
因此,一家公司必须拥有sId=1和sId=2以及tId=3和tId=4的产品才能获得资格。如果少了一个,就不应该出现。
我试着加入表格并进行
where pro.sId in ('1', '2')
and pro.tId in ('3','4')
但它没有给我任何公司。如有任何帮助,我们将不胜感激。
您可以使用聚合。假设products
表中每个company_id
没有重复的sld
或tld
:
select c.id
from companies c
inner join products p on p.company_id = c.id
where p.sld in (1, 2) or p.tld in (3, 4)
group by c.id
having count(*) = 4
如果有重复项,您可以将haven子句更改为:
having count(distinct p.sld) = 2 and count(distinct p.tld) = 2
有很多方法可以实现这一点。一种是:
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
);
另一个:
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);