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 1n55P8-00089E-9D for pgsql-sql@arkaria.postgresql.org; Wed, 05 Jan 2022 12:28:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1n55P5-0007tP-UG for pgsql-sql@arkaria.postgresql.org; Wed, 05 Jan 2022 12:28:31 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1n55P5-0007tG-AJ for pgsql-sql@lists.postgresql.org; Wed, 05 Jan 2022 12:28:31 +0000 Received: from mail-wr1-x429.google.com ([2a00:1450:4864:20::429]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1n55P2-00009m-Kh for pgsql-sql@lists.postgresql.org; Wed, 05 Jan 2022 12:28:30 +0000 Received: by mail-wr1-x429.google.com with SMTP id s1so82777838wrg.1 for ; Wed, 05 Jan 2022 04:28:28 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:from:mime-version:subject:date:message-id :references:cc:in-reply-to:to; bh=6u54eCqhAjeor47FlD/EQYszm1eQQb27UkrkCt3JVYY=; b=jRrS2ASO0vJf9G+2MhSb/xtnwEVkdIPqi9heFRhQptte4HpimMW1MQ25tT//oFnDTH BXM5b38o9G8sPwM7NaE0MLgHoEexsTQqnmUHiHLT6x6mttNQRHzxguiJm9YGsmyNlQeq RW47LzL63dZJ6iBl9rgeLy3jxa9TQXDVhlfqX+kq+nIYm/ROcfe0AmioK1mxQJpfnJc8 M2wbUY/0SJaSbI23CMiycR9uYsOELQURB8IiSneyVtrBOXWgRimNlZ8tBeilp8AEMRnK HyS4tOh0VL/RvkgTrCEanlCI9SIaQEP7DG5jXOYyv95gH2X50f7vnASJA32XYH56r+iR gFHg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:content-transfer-encoding:from:mime-version :subject:date:message-id:references:cc:in-reply-to:to; bh=6u54eCqhAjeor47FlD/EQYszm1eQQb27UkrkCt3JVYY=; b=IKB2nKSQ/BoIolYlEcayZhcZw39CgKF14E7MeS5Xj/1vLDUvjwDsqN7u8iZF0Gj0q+ t1Q/meTiaQ/wS1+wlZpr0zh5lfcm8ojIOUpft4wX1spjMOQNsPnmrgspdMB1tv19UcZZ FWY0x95+HYbpWsMPQlO8JP1OxmcdrsngX2G5TsPGrCDugF7z4Qg40w9FTzXBUMnQnT0J Wl9Z+Pa8n67KXOwycdm2Lqwy6HRaW8qAAIZEohoBjviWU7XxXYFX8uavE+XMj+9rcnHQ azq4WTntv318zznqfAHWOIppz0s91m7ZESeAsttlbv10FDz76yNxhRdapAOQkE4U4huj rm3w== X-Gm-Message-State: AOAM530MVWtSHHh8xyj+QR7Tj/k5NwsUGwHlv+H7Qo3RN24N+YZfF8L8 pso8p2XBngyP5c9TJMb9FB83BxBWl1nVTw== X-Google-Smtp-Source: ABdhPJzA7frtv7JbI1RvV5KRIMBOCpG/UHuV2PuFJKtxq+a1EwZS8jk6Icp0UBjHZ2Ue0vqgORJGKQ== X-Received: by 2002:a5d:6dad:: with SMTP id u13mr47717619wrs.604.1641385706152; Wed, 05 Jan 2022 04:28:26 -0800 (PST) Received: from smtpclient.apple ([195.158.249.26]) by smtp.gmail.com with ESMTPSA id u11sm2783898wmq.41.2022.01.05.04.28.25 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 05 Jan 2022 04:28:25 -0800 (PST) Content-Type: multipart/alternative; boundary=Apple-Mail-AC513397-A365-4D1C-820B-2E1052721E10 Content-Transfer-Encoding: 7bit From: Oliveiros Cristina Mime-Version: 1.0 (1.0) Subject: Re: Return product category with hierarchical info Date: Wed, 5 Jan 2022 12:28:23 +0000 Message-Id: <8578C971-C770-4EF7-8067-13A663F61A78@gmail.com> References: <24fd6621-db02-24a1-0808-fd11a17fe262@klingler.net> Cc: pgsql-sql@lists.postgresql.org In-Reply-To: <24fd6621-db02-24a1-0808-fd11a17fe262@klingler.net> To: Richard Klingler X-Mailer: iPhone Mail (19C56) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail-AC513397-A365-4D1C-820B-2E1052721E10 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable I=E2=80=99m also no expert, it=E2=80=99s been a decade or so since I do not u= se psql But maybe=20 Select level3.id, level3.name, level2.name, level1.name, =46rom category level3 Join category level2 On level2.Id =3D level3.parent Join category level1 On level1.Id =3D level2.parent Best, Oliver=20 Sent from Oliver=E2=80=99s iPhone > On 5 Jan 2022, at 12:19, Richard Klingler wrote: >=20 > =EF=BB=BFGood afternoon (o; >=20 >=20 > First of all, am I am totally no expert in using PGSQL but use it mainly f= or simple web applications... >=20 >=20 > Now I have a table which represents the categories for products in a hiera= rchical manner: >=20 > id | name | parent >=20 >=20 > So a top category is represented with parent being 0: >=20 > 1 | 'Living' | 0 >=20 >=20 > The next level would look: >=20 > 2 | 'Decoration' | 1 >=20 >=20 > And the last level (only 3 levels): >=20 > 3 | 'Table' | 2 >=20 >=20 > So far I'm using this query to get all 3rd level categories as I use the o= utput for datatables editor as a product can only belong to the lowest categ= ory: >=20 > select id, name from category > where parent in (select id from category where parent in (select id from c= ategory where parent =3D 0)) >=20 > But this has a problem as more than one 3rd level category can have the sa= me name, therefore difficult to distinguish in the datatables editor which o= ne is right. >=20 >=20 > So now my question (finally ;o): >=20 >=20 > Is there a simple query that would return all 3rd levels category ids and n= ames together with the concatenated names of the upper levels? Something lik= e: >=20 >=20 > 3 | 'Table' | 'Living - Decoration' >=20 >=20 > thanks in advance >=20 > richard >=20 >=20 >=20 > PS: If someone could recommend a good ebook or online resource for such st= upid questions, even better (o; >=20 >=20 >=20 >=20 --Apple-Mail-AC513397-A365-4D1C-820B-2E1052721E10 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable I=E2=80=99m also no expert, it=E2=80=99s be= en a decade or so since I do not use psql
 But maybe 

Select level3.id, level3.name, level2.name, level1.name,
=46rom category level3
Join category level2
On l= evel2.Id =3D level3.parent
Join category level1
On level= 1.Id =3D level2.parent

Best,
Oliver 

Sent from Oliver=E2=80=99s iPhone

On 5 Ja= n 2022, at 12:19, Richard Klingler <richard@klingler.net> wrote:
=EF=BB=BFGood afternoon (o;


Firs= t of all, am I am totally no expert in using PGSQL but use it mainly for sim= ple web applications...


No= w I have a table which represents the categories for products in a hierarchi= cal manner:

    id | name | p= arent


So a top category is= represented with parent being 0:

 &nb= sp;  1 | 'Living' | 0


The next level would look:

  &nb= sp; 2 | 'Decoration' | 1


A= nd the last level (only 3 levels):

 &n= bsp;  3 | 'Table' | 2


So far I'm using this query to get all 3rd level categories as I use the ou= tput for datatables editor as a product can only belong to the lowest catego= ry:

select id, name from categorywhere parent in (select id from category where parent in (select id f= rom category where parent =3D 0))

But this h= as a problem as more than one 3rd level category can have the same name, the= refore difficult to distinguish in the datatables editor which one is right.=


So now my question (final= ly ;o):


Is there a simple q= uery that would return all 3rd levels category ids and names together with t= he concatenated names of the upper levels? Something like:
<= /span>

    3 | 'Table' | 'Living - D= ecoration'


thanks in advan= ce

richard



PS: If someone could recommend a good ebo= ok or online resource for such stupid questions, even better (o;
<= span>



= --Apple-Mail-AC513397-A365-4D1C-820B-2E1052721E10--