Re: Return product category with hierarchical info

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(at)klingler(dot)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;
>>
>>
>>
>>