sql - 基于两个多对多关系选择行

我有三个表：product，tag和tag_collection（以及两个连接表）（标记集合是一组命名的标记）

现在，假设我有一个名为sugar free cola的标记集合，其id为：1，并且它有与之相关联的这些标记：soda，cola，sugar free。
我有几个产品的id是：4，5和6，它们有相同的标签。
我需要使用什么查询才能从tag廑collection id4中获取产品5、6和1。
换言之，我怎样才能得到所有标签也在给定标签集合中的产品？
我尝试用LEFT JOIN查询每个表，但这给了我一个产品，它有tag_collection中的一个标记。
我正在使用Postgresql 11.1。

最佳答案

你可以把两个连接表连在一起。然后聚合并计数标记：

select pt.product_id
from product_tags pt join
     (select tct.*, count(*) over (partition by tct.tag_collection_id) as cnt
      from tag_collection_tag tct
     ) tct
     on tct.tag_id = pt.tag_id
where tct.tag_collection_id = 1
group by pt.product_id, tct.cnt
having count(*) = tct.cnt  -- number of matches equals the number of tags