We have a hierarchy which looks this:
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
We are using "PostgreSQL 9.3.5" And these are the tables for this case, you can see in my above query (and below ERD) which connections I would like to accomplish.
EDIT 2: This was my query for mysql (here we use taxonomies and not categories):
SELECT T2.id, T2.taxonomy, T2.parent, T2.terms_id, terms.term FROM ( SELECT @r AS _id, (SELECT @r := parent FROM term_taxonomies WHERE id = _id) AS parent, @l := @l + 1 AS lvl FROM (SELECT @r := 1, @l := 0) vars, term_taxonomies m WHERE @r <> 0) T1 JOIN term_taxonomies T2 ON T1._id = T2.id JOIN terms ON T2.terms_id = terms.id ORDER BY T1.lvl DESC;
But this only works for mysql