CODEWITHSUNDEEP
mysqlsqlselectrelational-division
David Lok
David Lok

Reputation: 189

SELECT value x WHERE x has same unknown value AND WHERE value y is in array

I have a table that stores product groups.

table "products_groups"

id  |  id_group | id_product
============================
 1  |  1        | 1
 2  |  1        | 2
 3  |  1        | 3
 4  |  2        | 1
 5  |  2        | 2
 6  |  3        | 2

I need a SQL query that will find the id_group that contains all id_product's that are in the given array.

product_ids = array(1);       // should return no results
product_ids = array(1,2);     // should only return id_group 2
product_ids = array(1,2,3);   // should only return id_group 1
product_ids = array(1,2,3,4); // should return no results

I played/searched around, ended up stuck at

SELECT p1.id_group
FROM products_groups p1, products_groups p2
WHERE p1.id <> p2.id
AND p1.id_group = p2.id_group
AND (
    p1.id_product = 1 
    OR p1.id_product = 2
    OR p1.id_product = 3
)

But it obviously is not giving me the result I am looking for. I don't know if I am thinking too simple or too complex.

Notes: Of course the id_product values will be dynamically generated in the SQL. It will eventually be used with PHP/Codeigniter

Background info: Each product has a price, but products can be in a product group which has a package price. That is why I need to know for each order if the products are in a group.

Upvotes: 1

Views: 746

Answers (1)

John Woo
John Woo

Reputation: 263763

This problem is called Relational Division

SELECT  id_group
FROM    products_groups
WHERE   id_product IN (1,2)
GROUP   BY id_group
HAVING  COUNT(*) = 2

or something like this,

SELECT  id_group
FROM    products_groups
GROUP   BY id_group
HAVING  SUM(id_product IN (1,2)) = COUNT(*) AND
        COUNT(*) = 2

Upvotes: 4

Related Questions