Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1n55YN-00007c-AD for pgsql-sql@arkaria.postgresql.org; Wed, 05 Jan 2022 12:38:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1n55YL-0001tB-Nc for pgsql-sql@arkaria.postgresql.org; Wed, 05 Jan 2022 12:38:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1n55YL-0001t2-Dw for pgsql-sql@lists.postgresql.org; Wed, 05 Jan 2022 12:38:05 +0000 Received: from mail.klingler.net ([5.189.138.105]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1n55YF-0000Zo-5U for pgsql-sql@lists.postgresql.org; Wed, 05 Jan 2022 12:38:05 +0000 Content-Type: multipart/alternative; boundary="------------gVVEa1rHI5LKXmhB2IGdfJjY" DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=klingler.net; s=2020; t=1641386277; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=Qsu8wO8lSwMxC6F0gmllnEzEGKPvkEHEy7qbKc6E8F4=; b=OLfWurQwAf9PwfU20FjQlYXXONIiP/pCEC+Admve0QkGWj+0hxdQRNp9AX9uc+Cs3Cgppo Rq38xWjqFjZGGOQbS8KbaUutUInnJn78Yk4KOXyizp/OYoX04DaSqYtfh0jPCWzXWN8khZ zVzao/APGLmlmbuUQPzUO6m3HOoc+fA= Message-ID: Date: Wed, 5 Jan 2022 13:37:55 +0100 MIME-Version: 1.0 Subject: Re: Return product category with hierarchical info Content-Language: en-US To: pgsql-sql@lists.postgresql.org References: <24fd6621-db02-24a1-0808-fd11a17fe262@klingler.net> <8578C971-C770-4EF7-8067-13A663F61A78@gmail.com> From: Richard Klingler In-Reply-To: <8578C971-C770-4EF7-8067-13A663F61A78@gmail.com> Authentication-Results: ORIGINATING; auth=pass smtp.auth=richard@klingler.net smtp.mailfrom=richard@klingler.net List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------gVVEa1rHI5LKXmhB2IGdfJjY Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 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; >> >> >> >> --------------gVVEa1rHI5LKXmhB2IGdfJjY Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

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;




--------------gVVEa1rHI5LKXmhB2IGdfJjY--