选择数据透视表具有所有ID的行 [英] Select rows where pivot has ALL ids

问题描述

案例: 我希望所有具有company_id 8和13的公司，但只有它们具有所有product_id(1,2,4,8)的公司.

Case: I want all companies that have company_id 8 and 13, but ONLY if they have ALL product_ids (1,2,4,8).

问题: company_id 13没有product_ids 4和8，但仍显示在查询结果中，如下所示.如果cp.product_id IN(1,2,4,8)拥有这些值之一，就好像返回了公司，但我需要它们在数据透视表company_products中具有所有这些值.

Problem: company_id 13 does not have product_ids 4 and 8, but still shows up in the results of the query as below. It seems as though cp.product_id IN (1,2,4,8) returns the company if it has ONE of these values, but I need them to have ALL these values in pivot table company_products.

SELECT
    c.id as company_id,
    cp.product_id
FROM
    `companies` as c
    LEFT JOIN `company_products` cp ON cp.company_id = c.id
WHERE
    c.id IN (8, 13)
    AND cp.product_id IN (1,2,4,8)
GROUP BY
    c.id

我的猜测是我应该使用IN以外的其他东西，但是我的研究并未引导我找到正确的解决方案.感谢您的帮助.

My guess is I should use something other than IN, but my research has not lead me to the correct solution. Your help is appreciated.

推荐答案

您可以在查询中添加having子句，以确保每组c.id都具有全部四个cp.product_id:

You could add a having clause to your query to make sure that every group of c.id has all four cp.product_id:

SELECT
    c.id as company_id
FROM
    `companies` as c
    LEFT JOIN `company_products` cp ON cp.company_id = c.id
WHERE
    c.id IN (8, 13)
    AND cp.product_id IN (1,2,4,8)
GROUP BY
    c.id
HAVING COUNT(DISTINCT cp.product_id) = 4;    

示例SQL小提琴

如果您还需要产品详细信息，则可以将上述查询用作派生表，并将其与产品表联接.

If you need the product details too you can use the above query as a derived table and join it with the product table.

这篇关于选择数据透视表具有所有ID的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！