Get products of category plus all child categories till leaf nodes 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

EDIT:

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 &lt;&gt; 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
If your data structure is fixed and out of your control, then the best way to do this is to use a recursive common table expression (CTE) like in this question.

If you can change the structure then there are ways to make such queries considerably more efficient. What you have there is often called a "naive tree" - while it allows easy construction and easy navigation up and down by one level at a time, more complex queries including those that need to consider arbitrary depth (like your question) can be a pain.

The common alternatives are:


Store the full path of each node as well as the parent relationship, and index it. This denormalises your data a little but makes "this node down" queries easy because you can search for WHERE tree_path LIKE current_node_path+'%' or to exlude the current node and list just those below it WHERE tree_path LIKE current_node_path+'/%'. Of course you have the extra work of updating the paths when nodes are moved - this is probably best done by trigger or if your application always uses stored procs for updating the tree instead of direct table access it is clear (and perhaps more efficient) to include the logic there.
Store a matrix of relationships, so for the tree 1->2, 1->3, 2->4, 2->5, 5->6 you would store:
n1  n2  depth
2   1      1
3   1      1
4   2      1
4   1      2
5   2      1
5   1      2
6   5      1
6   2      2
6   1      3
Again you have extra data to maintain, but it makes certain varieties of query more efficient and easer to write. For all under node 5 it is WHERE n1=5. Some would store the self relation (i.e. n1=5, n2=5, d=0) too to avoid unions when you want the node and all below - then "5 and below" is WHERE n1=5 and "below 5" is WHERE n1=5 AND depth&gt;0.


There is a good chapter covering this in more detail in this book which I recommend anyone working with database has a read through - it has a good tone for the relatively inexperienced and the experienced will be reminded of important things they've forgotten! If you want to go into real depth there are whole books about handing hierarchical data in SQL and other relational systems. The usual recommendation is "Joe Celko's Trees and Hierarchies in SQL for Smarties".