多对多查询

Question

我在多对多关系中有两个表products和sections，还有一个连接表products_sections。一个产品可以在一个或多个部分(新的、汽车的、飞机的、旧的)。

Products
id    name
-----------------
1     something
2     something_else
3     other_thing


Sections
id    name
-----------------
1     new
2     car

Products_sections
product_id     section_id
--------------------------
1              1
1              2
2              1
3              2

我想提取所有的产品，都在新的和汽车部分。在本例中，返回的结果应该是product 1。什么是正确的mysql查询来获得此结果？

Answer 1

SELECT Products.name
FROM Products
WHERE NOT EXISTS (
  SELECT id
  FROM Sections
  WHERE name IN ('new','car')
  AND NOT EXISTS (
    SELECT *
    FROM Products_sections
    WHERE Products_sections.section_id = Sections.id
    AND Products_sections.product_id = Products.id
  )
)

也就是说，从该产品的Products_sections表格中选择不缺少任何所需Section.id值的产品。

Answer和‘s的评论：

你可以把

  NOT EXISTS (<select query>)

转换为WHERE子句，就像其他谓词一样。描述的结果集中没有行，则计算结果为TRUE。

逐步地，下面是如何得到这个查询的答案：

步骤1.要求识别“在'new‘和'car’部分中”的所有产品。

步骤2.如果“new”和“car”两个部分都包含产品，则该产品同时位于“new”和“car”部分。同样，如果一个产品在“new”和“car”两个部分中都没有包含该产品，那么这两个部分都不包含该产品。(请注意双重否定：Note不能使包含。)再次重申，我们希望所有有no required failing部分的产品都包含该产品。

所需的部分如下：

SELECT id
FROM Sections
WHERE name IN ('new','car')

因此，所需的产品如下：

SELECT Products.name
FROM Products
WHERE NOT EXISTS ( -- there does not exist
  SELECT id  -- a section
  FROM Sections
  WHERE name IN ('new','car') -- that is required
   AND (the section identified by Sections.id fails to contain the product identified by Products.id)
)

步骤3.如果给定部分(如'new‘或'car')的中存在与给定部分和特定产品对应的行，则Products_sections确实包含该特定产品。因此，如果在Products_sections中没有这样的行，则给定节将使无法包含特定的产品。

步骤4.如果下面的查询包含行，则section_id部分包含product_id产品：

SELECT *
FROM Products_sections
WHERE Products_sections.section_id = Sections.id
AND Products_sections.product_id = Products.id

因此，如果上面的查询没有在其结果中生成一行，或者如果不是 ()，那么section_id部分的将无法包含产品(这就是我们需要表达的)。

看起来很复杂，但一旦你明白了，就会明白:所有必需的项目都存在吗？可以，只要不存在不存在的必需项。

Answer 2

我总是这样做：

从你想要得到的(products)开始，然后遍历你的查询表(products_sections)，找到你想要过滤的(sections)。这样，你就可以清楚地看到你正在寻找的东西，而且你永远不需要记住代理键(拥有代理键是一件很棒的事情，而不是记住它)。

select distinct
    p.name
from
    products p
    inner join products_sections ps on
        p.product_id = ps.product_id
    inner join sections s1 on
        ps.section_id = s1.section_id
    inner join sections s2 on
        ps.section_id = s2.section_id
where
    s1.name = 'new'
    and s2.name = 'car'

瞧。三个inner join，您就有了一个很好的、清晰的、简洁的查询，很明显它带回了什么。希望这能有所帮助！

Answer 3

SELECT product_id, count(*) AS TotalSection
FROM Products_sections
GROUP BY product_id
WHERE section_id IN (1,2)
HAVING TotalSection = 2;

看看这在mysql中是否有效。