agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Oliveiros Cristina <oliveiros.cristina@gmail.com>
To: Richard Klingler <richard@klingler.net>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Return product category with hierarchical info
Date: Wed, 5 Jan 2022 12:28:23 +0000
Message-ID: <8578C971-C770-4EF7-8067-13A663F61A78@gmail.com> (raw)
In-Reply-To: <24fd6621-db02-24a1-0808-fd11a17fe262@klingler.net>
References: <24fd6621-db02-24a1-0808-fd11a17fe262@klingler.net>

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;
> 
> 
> 
> 

view thread (5+ messages)  latest in thread

Message-ID: <8578C971-C770-4EF7-8067-13A663F61A78@gmail.com>
Permalink:  ../8578C971-C770-4EF7-8067-13A663F61A78@gmail.com/
Also on:    postgresql.org/message-id/8578C971-C770-4EF7-8067-13A663F61A78@gmail.com

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: oliveiros.cristina@gmail.com, richard@klingler.net, pgsql-sql@lists.postgresql.org
  Subject: Re: Return product category with hierarchical info
  In-Reply-To: <8578C971-C770-4EF7-8067-13A663F61A78@gmail.com>

* 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