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 13:00:09 +0000
Message-ID: <BA76C02E-C4C8-4EA4-BE9E-7299EE21ACC8@gmail.com> (raw)
In-Reply-To: <ff8e4a3a-a65e-14ff-8da9-a4b89661ec06@klingler.net>
References: <ff8e4a3a-a65e-14ff-8da9-a4b89661ec06@klingler.net>
No worries
Glad it helped !
Best,
Oliver
Sent from Oliver’s iPhone
> On 5 Jan 2022, at 12:38, Richard Klingler <richard@klingler.net> wrote:
>
>
> 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;
>>>
>>>
>>>
>>>
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: <BA76C02E-C4C8-4EA4-BE9E-7299EE21ACC8@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox