Re: Return product category with hierarchical info

On Wed, Jan 5, 2022 at 4:38 AM Richard Klingler <richard(at)klingler(dot)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(at)klingler(dot)net>
> <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;
>
>
>
That query will work fine. But if you have variable tree depth (sometimes
1, 2, 3, or n), you might consider using a recursive query, which doesn't
care how deep you have to query:
https://www.postgresql.org/docs/current/queries-with.html

I haven't tried to create a demo using your data, but let us know if you
can't figure it out (assuming you even need recursion for your use case).

Steve