sql - 奇怪的sql查询实现连接表

Question

我需要一些有关 SQL 查询的帮助。让我描述一下场景

我有三个表，dispensaries、goods 和 products。

products 记录有两个 fk，good_id 和 dispensary_id 让我可以通过产品从药房访问商品.goods 也有一个名为 name

的属性

现在我想解决的情况如下:

对于一组提供的商品名称([good_name1, good_name2, etc])，我想获得数组中包含所有商品的所有药房(与这些名称匹配的商品) .

再举个例子:

 good1 whose names is  good_1 belongs to dispensary1
 good1 whose names is  good_1 belongs to dispensary2
 good2 whose names is  good_2 belongs to dispensary1

所以我需要创建一个 SQL 查询，使用提供的商品数组 [good1, good2] 返回唯一的 dispesary1

提前致谢。

Answer 1

实现这一点的一种方法是使用 INTERSECT，为每个子句生成一个集合 — 这意味着您必须将列表展开为 n 个查询，一个对于每个项目:

SELECT dispensary.id, dispensary.name
FROM products
INNER JOIN goods ON goods.id = products.goods_id
INNER JOIN dispensary ON dispensary.id = products.dispensary_id
WHERE goods.name = 'good_1'
INTERSECT
SELECT dispensary.id, dispensary.name
FROM products
INNER JOIN goods ON goods.id = products.goods_id
INNER JOIN dispensary ON dispensary.id = products.dispensary_id
WHERE goods.name = 'good_2'
INTERSECT
SELECT dispensary.id, dispensary.name
FROM products
INNER JOIN goods ON goods.id = products.goods_id
INNER JOIN dispensary ON dispensary.id = products.dispensary_id
WHERE goods.name = 'good_3'

这样，您将只会获得所有集合中的结果。