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: Re: Return product category with hierarchical info
Date: Wed, 5 Jan 2022 13:37:55 +0100
Message-ID: <ff8e4a3a-a65e-14ff-8da9-a4b89661ec06@klingler.net> (raw)
In-Reply-To: <8578C971-C770-4EF7-8067-13A663F61A78@gmail.com>
References: <24fd6621-db02-24a1-0808-fd11a17fe262@klingler.net>
	<8578C971-C770-4EF7-8067-13A663F61A78@gmail.com>

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


On 1/5/22 13:28, Oliveiros Cristina wrote:
> 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: <ff8e4a3a-a65e-14ff-8da9-a4b89661ec06@klingler.net>
Permalink:  ../ff8e4a3a-a65e-14ff-8da9-a4b89661ec06@klingler.net/
Also on:    postgresql.org/message-id/ff8e4a3a-a65e-14ff-8da9-a4b89661ec06@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: <ff8e4a3a-a65e-14ff-8da9-a4b89661ec06@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