I'm struggling with writing an SQL query to display the leaf node (last node) along with their parents names without having to use a procedural function in php (I need to do this in the SQL) and I'm wondering if someone can help me. Im using MySQL as the db.
I'm using the adjacency list model for storing the data. What I want returned is a set of data that displays the leaf node, but with some text in front of it that shows the parent-child relationship...for example, the out put would look something like this:
Category 2 | *this would be a leaf node that doesn't have any children*
Category 1 | Subcategory 1B
Category 1 | Subcategory 1A | Subcategory 1AA
The table looks like this:
Category_id Name Parent_Cat_ID
1 Category 1 NULL
2 Category 2 NULL
3 Subcategory 1B 1
4 Subcategory 1A 1
5 Subcategory 1AA 4
The SQL I've tried below works fine, but it is only giving me the name of the Leaf Node and if I could simply display the name of the parents in front of the leaf node it would work for my application. Here's the SQL:
SELECT t1.name
FROM category AS t1
LEFT JOIN category AS t2 ON t1.category_id = t2.Parent_Cat_ID
WHERE t2.category_id IS NULL
This is the output I'm currently getting:
Category 2 |
Subcategory 1B |
Subcategory 1AA |
I need to make this work with an "nth" number of category/subcategory nestings...in other words, the users will be adding and deleting the categories/subcategories to the nth level.
I hope this makes enough sense the way I've written it. Any ideas are appreciated. Thanks.