您可以通过将ID汇总到一个数组中,然后将其与预期ID列表进行比较来做到这一点:
select v.*
from venues v
join amenity_venue av ON av.venue_id = v.id
group by v.id
having array_agg(av.amenity_id) @> array['aaa', 'bbb'];
上面的示例假定已将venue.id
其声明为主键(因为group by
)。
如果您只想传递便利设施名称,则实际上并不需要对查询中的ID进行硬编码:
select v.*
from venues v
join amenity_venue av ON av.venue_id = v.id
group by v.id
having array_agg(av.amenity_id) @> array(select id
from amenities
where name in ('first amenity', 'second amenity'));
在线示例:https://rextester.com/FNNVXO34389