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 1n55ts-0000oO-4T for pgsql-sql@arkaria.postgresql.org; Wed, 05 Jan 2022 13:00:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1n55tp-0006G0-QY for pgsql-sql@arkaria.postgresql.org; Wed, 05 Jan 2022 13:00:17 +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 1n55tp-0006Fr-64 for pgsql-sql@lists.postgresql.org; Wed, 05 Jan 2022 13:00:17 +0000 Received: from mail-wr1-x42a.google.com ([2a00:1450:4864:20::42a]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1n55tl-0000Oy-FT for pgsql-sql@lists.postgresql.org; Wed, 05 Jan 2022 13:00:16 +0000 Received: by mail-wr1-x42a.google.com with SMTP id k18so46136923wrg.11 for ; Wed, 05 Jan 2022 05:00:13 -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=J+rKvHyO17AKQ0BeI4oEOfRZIyW4NGHfxfD64PmQ6B4=; b=MYVNKpNbcn0m2fzpZsyCJOqoIitSUQ/lNnMyCp5u8lnSllM2UGz70aYkH5U2mNlmKU fpFMAU717adHI2ut/7EvGvkVYOketWN2dMxfCLxJGoMz9EEDNyaGu+OpOZ04EKSik0wd uLGCnsX0qCoDVPxj4Xzz46IGeXATOnZe1N3UoMCNRujSZOyJP/bx51g/UNqjWAJAg0zM DAX1CqL3I/KpP3ugBu4cGnuLtQm3pAnFt9le5Vw+o9blv2NS1cUuIc2310ypRw56ru/+ gOBbz8WxpNFU5uODIoC0bdH/bzxKX99O8fNvYBAsTw0/+bPBfB6R3WufJ9eCldRm20Uc QmzQ== 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=J+rKvHyO17AKQ0BeI4oEOfRZIyW4NGHfxfD64PmQ6B4=; b=LAv/IACkTR5hsaJUc1wIXjc3G4vYswfH17fD5tuZ8j4Ksf/zzSn3A1foiQG+a3sKi8 F6zB+Jo1L6x43yTdND2oKGaHs4h2RRuOMa4POoiqZ9csOqRuoweQH9Mgp60z7nieq84p DfGfH0eVMqoqyQa6vVH+vl0fLNNxjgmCzzu9JlTAf1BU+sZSesPvmTp6np4mcWzLZPj+ RfevicqreQcnVBgev5BRnrduPEOjOUyGpgs00qcyXMzgOfX6Kt0c/hPK9I2K2UDAQOyn vrUGH5MxVMbwVBFcQXbnxKkrZirAFzTPcf80tT1knETkq7kgDRrU7n4oye4PGQ3rK38Y sFsA== X-Gm-Message-State: AOAM533OMcRw5guZTuTFi4iZ9aE2YGmKiRYqwdt3YY1Z+svfcsyrOWLa edS8KXeyME3dzt+pufcEXmeKbjjW/W0YQw== X-Google-Smtp-Source: ABdhPJwf0P6M26/9dTT4xtncw2hzo+B4k0MPF1vtykfKOW30triIq5nxgpEnRSeBv5ntrQTvYp3A8A== X-Received: by 2002:a5d:554e:: with SMTP id g14mr45715229wrw.353.1641387611463; Wed, 05 Jan 2022 05:00:11 -0800 (PST) Received: from smtpclient.apple ([195.158.249.10]) by smtp.gmail.com with ESMTPSA id u20sm2868408wml.45.2022.01.05.05.00.10 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 05 Jan 2022 05:00:10 -0800 (PST) Content-Type: multipart/alternative; boundary=Apple-Mail-DEF8CC7D-9A0F-44BE-B4A8-B849FC9FF37D 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 13:00:09 +0000 Message-Id: References: Cc: pgsql-sql@lists.postgresql.org In-Reply-To: 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-DEF8CC7D-9A0F-44BE-B4A8-B849FC9FF37D Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable No worries Glad it helped ! Best, Oliver=20 Sent from Oliver=E2=80=99s iPhone > On 5 Jan 2022, at 12:38, Richard Klingler wrote: >=20 > =EF=BB=BF > Hello Oliver >=20 >=20 >=20 > Exactly that's it...I knew some "join" would be involved...but couldn't fi= nd the right example ;-) >=20 >=20 >=20 > thanks for the quick help :-) >=20 > richard >=20 >=20 >=20 > On 1/5/22 13:28, Oliveiros Cristina wrote: >> I=E2=80=99m also no expert, it=E2=80=99s been a decade or so since I do n= ot use psql >> But maybe=20 >>=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 >>=20 >> Best, >> Oliver=20 >>=20 >> Sent from Oliver=E2=80=99s iPhone >>=20 >>> 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= for simple web applications... >>>=20 >>>=20 >>> Now I have a table which represents the categories for products in a hie= rarchical 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= output for datatables editor as a product can only belong to the lowest cat= egory: >>>=20 >>> select id, name from category >>> where parent in (select id from category where parent in (select id from= category where parent =3D 0)) >>>=20 >>> But this has a problem as more than one 3rd level category can have the s= ame 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 an= d names together with the concatenated names of the upper levels? Something l= ike: >>>=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 s= tupid questions, even better (o; >>>=20 >>>=20 >>>=20 >>>=20 --Apple-Mail-DEF8CC7D-9A0F-44BE-B4A8-B849FC9FF37D Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable No worries
Glad it helped !
<= br>

Best,
Oliver 

Sent from Oliver=E2=80=99s iPhon= e

On 5 Jan 2022, a= t 12:38, Richard Klingler <richard@klingler.net> wrote:

=EF=BB=BF =20 =20 =20

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=E2=80=99m also no expert, it=E2=80=99s been a decade or so since I d= o not use psql
 But maybe 

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 

Sent from Oliver=E2=80=99s iPhone

On 5 Jan 2022, at 12:19, Richard Klingler <richard@klingler.net> wrote:

=EF=BB=BFGood 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 =3D 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;
=



=20
= --Apple-Mail-DEF8CC7D-9A0F-44BE-B4A8-B849FC9FF37D--