Thread: Return product category with hierarchical info

Good afternoon (o;


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;

Hello Oliver

Exactly that's it...I knew some "join" would be involved...but couldn't find the right example ;-)

thanks for the quick help :-)

richard

I’m also no expert, it’s been a decade or so since I do not use psql

 But maybe 

Select level3.id, level3.name, level2.name, level1.name,

From category level3

Join category level2

On level2.Id = level3.parent

Join category level1

On level1.Id = level2.parent

Best,

Oliver 

Sent from Oliver’s iPhone

On 5 Jan 2022, at 12:19, Richard Klingler <richard@klingler.net> wrote:

Good afternoon (o;


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;