agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Richard Klingler <richard@klingler.net>
To: pgsql-sql@lists.postgresql.org
Subject: Return product category with hierarchical info
Date: Wed, 5 Jan 2022 13:19:11 +0100
Message-ID: <24fd6621-db02-24a1-0808-fd11a17fe262@klingler.net> (raw)

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;

view thread (5+ messages)  latest in thread

Message-ID: <24fd6621-db02-24a1-0808-fd11a17fe262@klingler.net>
Permalink:  ../24fd6621-db02-24a1-0808-fd11a17fe262@klingler.net/
Also on:    postgresql.org/message-id/24fd6621-db02-24a1-0808-fd11a17fe262@klingler.net

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: richard@klingler.net, pgsql-sql@lists.postgresql.org
  Subject: Re: Return product category with hierarchical info
  In-Reply-To: <24fd6621-db02-24a1-0808-fd11a17fe262@klingler.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox