First of all, am I am totally no expert in using PGSQL but use it mainly for simple web applications...
Now I have a table which represents the categories for products in a hierarchical manner:
id | name | parent
So a top category is represented with parent being 0:
1 | 'Living' | 0
The next level would look:
2 | 'Decoration' | 1
And the last level (only 3 levels):
3 | 'Table' | 2
So far I'm using this query to get all 3rd level categories as I use the output for datatables editor as a product can only belong to the lowest category:
select id, name from category where parent in (select id from category where parent in (select id from category where parent = 0))
But this has a problem as more than one 3rd level category can have the same name, therefore difficult to distinguish in the datatables editor which one is right.
So now my question (finally ;o):
Is there a simple query that would return all 3rd levels category ids and names together with the concatenated names of the upper levels? Something like:
3 | 'Table' | 'Living - Decoration'
thanks in advance
richard
PS: If someone could recommend a good ebook or online resource for such stupid questions, even better (o;