jeudi 18 décembre 2014

Get products of category plus all child categories till leave nodes


We have a hierarchy which looks this: enter image description here


Now the problem is that most products are only connected with 1 category, which is most of the time the lowest level.


I am trying to get ALL products below a given category. So If select one category, I want ALL products from current category and ALL categories below. So this didn't work:



SELECT "category"."id"
,"products"."name"
FROM "products"
INNER JOIN "product_categories" ON "products"."id" = "product_categories"."product_id"
INNER JOIN "categories" ON "product_categories"."category_id" = "categories"."id"
WHERE "categories"."parent_id" = 0


Anyone who can help me out? BTW, we are using PostgreSQL





Aucun commentaire:

Enregistrer un commentaire