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