SQL query on Many to Many with intersection

Question

I am working with django-categories which uses django-mptt under the hood. (That implicates db structure that I am using.)

I have some products:

product       
--------      
id | name     
1  | Apple    
2  | Orange   
3  | Tomato

Categories:

categories
----------
id | name       | parent | tree_id | level
1  | Type       | null   | 1       | 1
2  | Fruit      | 1      | 1       | 2
3  | Vegetable  | 1      | 1       | 2
4  | Color      | null   | 2       | 1
5  | Orange     | 4      | 2       | 2
6  | Red        | 4      | 2       | 2
7  | Green      | 4      | 2       | 2
8  | Dark green | 7      | 2       | 3
9  | Orange     | 4      | 2       | 2

Which is tree with single root (nodes have also fields order, left, right but I don't think that it is relevant here):

root/
├── Type/
│   ├── Fruit
│   ├── Vegetable
└── Color/
    ├── Red
    ├── Green
    |   └── Dark green
    └── Orange

And M2M table:

product_categories
------------------
id | product_id | category_id
1  | 1          | 2
2  | 1          | 6
3  | 2          | 2
4  | 2          | 9
5  | 3          | 3
6  | 3          | 5

So I am having all categories in one tree, but I can group them by tree_id. Now I would like to find products that are Fruits and are Red OR Orange (so to get Apple an Orange)

I was expecting that query to give me what I want:

SELECT DISTINCT 
"product"."id", "product"."name" 
FROM
"product" 
INNER JOIN "product_categories" ON ("product"."id" = "product_categories"."product_id") 
WHERE 
("product_categories"."category_id" IN ('2') 
AND "product_categories"."category_id" IN ('6', '9'))

but I am getting no results.

It could be achieved by querying multiple times, once per tree, and then looking for intersection, but I believe it is doable by single query.

Attach sample output please. This is definitely wrong: `product_categoryes IN ('2') AND ... IN ('6','9')`. The value can't be 2 and 6 or 2 and 9 at once. — Kamil Gosciminski, Sep 14 '18 at 11:33

score 2 · Accepted Answer · edited Sep 14 '18 at 12:27

Try this:

SELECT DISTINCT 
"product"."id", "product"."name" 
FROM
"product" 
INNER JOIN "product_categories" ON ("product"."id" = "product_categories"."product_id") 
WHERE 
("product_categories"."category_id" IN ('6', '9'))
INTERSECT
SELECT DISTINCT 
"product"."id", "product"."name" 
FROM
"product" 
INNER JOIN "product_categories" ON ("product"."id" = "product_categories"."product_id") 
WHERE 
("product_categories"."category_id" IN ('2'))

SQL query on Many to Many with intersection

1 Answers1