Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 3133B19713C4 for ; Tue, 6 Dec 2011 10:40:07 -0400 (AST) Received: from iota.marktest.pt ([80.251.174.118] helo=hermes.marktest.pt) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RXwBe-0006SY-5K for pgsql-sql@postgresql.org; Tue, 06 Dec 2011 14:40:07 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by hermes.marktest.pt (Postfix) with ESMTP id 79A5E1A814F; Tue, 6 Dec 2011 14:39:48 +0000 (WET) X-Virus-Scanned: amavisd-new at marktest.pt Received: from hermes.marktest.pt ([127.0.0.1]) by localhost (hermes.marktest.pt [127.0.0.1]) (amavisd-new, port 11124) with ESMTP id DrOOuf5Pnzu4; Tue, 6 Dec 2011 14:39:48 +0000 (WET) Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by hermes.marktest.pt (Postfix) with ESMTP id 47D8B1A8148; Tue, 6 Dec 2011 14:39:48 +0000 (WET) Message-ID: <37CE275E52C24BA189FDD6BA0B905F95@marktestcr.marktest.pt> From: "Oliveiros d'Azevedo Cristina" To: "Abhinandan Raghavan" , References: <4EDE1F3F.6060807@unige.ch> Subject: Re: Self-Join Date: Tue, 6 Dec 2011 14:39:47 -0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002F_01CCB424.E707C450" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Pg-Spam-Score: -3.1 (---) X-Archive-Number: 201112/5 X-Sequence-Number: 36066 This is a multi-part message in MIME format. ------=_NextPart_000_002F_01CCB424.E707C450 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Howdy, Abhinandan, A quick and dirty solution might be this : SELECT * FROM=20 ( SELECT a.name,MAX(b.value) as height FROM original a LEFT JOIN original b ON a.name =3D b.name AND b.attribute =3D 'Height' GROUP BY a.name ) height NATURAL JOIN ( SELECT a.name,MAX(b.value) as weigth FROM original a LEFT JOIN original b ON a.name =3D b.name AND b.attribute =3D 'Weight' GROUP BY a.name ) weight NATURAL JOIN=20 ( SELECT a.name,MAX(b.value) as age FROM original a LEFT JOIN original b ON a.name =3D b.name AND b.attribute =3D 'Age' GROUP BY a.name ) age The thing is that it doesn't scale well if you have many more items = beyond three... Best, Oliveiros ----- Original Message -----=20 From: Abhinandan Raghavan=20 To: pgsql-sql@postgresql.org=20 Sent: Tuesday, December 06, 2011 1:57 PM Subject: [SQL] Self-Join Hi, I'm looking to frame an SQL statement in Postgres for what's explained = in the attached image.=20 The original table is at the top and is called NAV (Short for Name, = Attribute, Value). I want to create a view (NWHA_View) involving values = from within (presumably from a self join). I would've normally created a = view in the following way:=20 SELECT A.NAME,=20 A.VALUE AS WEIGHT, B.VALUE AS HEIGHT, C.VALUE AS AGE FROM NAV A, NAV B, NAV C WHERE A.NAME =3D B.NAME AND A.NAME =3D C.NAME AND A.ATTRIBUTE =3D 'Weight' AND B.ATTRIBUTE =3D 'Height' AND C.ATTRIBUTE =3D 'Age' The only problem when I create a view with the above select statement = is that when there are no entries for the field name "AGE" (in the case = of David), then the row does not get displayed. What's the way out in = Postgresql? I know the way it is addressed in Oracle but it doesn't seem = to work in Postgresql. Thanks. Abhi -------------------------------------------------------------------------= ----- --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ------=_NextPart_000_002F_01CCB424.E707C450 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Howdy, Abhinandan,
 
A quick and dirty solution might be = this=20 :
 
SELECT *
FROM
(
SELECT=20 a.name,MAX(b.value) as height
FROM original a
LEFT JOIN original = b
ON=20 a.name =3D b.name
AND b.attribute =3D 'Height'
GROUP BY = a.name
)=20 height
NATURAL JOIN
(
SELECT a.name,MAX(b.value) as = weigth
FROM=20 original a
LEFT JOIN original b
ON a.name =3D b.name
AND = b.attribute =3D=20 'Weight'
GROUP BY a.name
) weight
NATURAL JOIN
(
SELECT=20 a.name,MAX(b.value) as age
FROM original a
LEFT JOIN
 =20 original
 b
ON a.name =3D b.name
AND b.attribute =3D = 'Age'
GROUP BY=20 a.name
) age
 
The thing is that it doesn't scale well = if you have=20 many more items beyond three...
 
Best,
Oliveiros
----- Original Message -----
From:=20 Abhinandan Raghavan =
Sent: Tuesday, December 06, = 2011 1:57=20 PM
Subject: [SQL] Self-Join

Hi,

I'm looking to frame an SQL statement in = Postgres=20 for what's explained in the attached image.

The original table = is at=20 the top and is called NAV (Short for Name, Attribute, Value). I want = to create=20 a view (NWHA_View) involving values from within (presumably from a = self join).=20 I would've normally created a view in the following way:


SELECT A.NAME, 
             A.VALUE AS = WEIGHT,
             B.VALUE AS = HEIGHT,
             C.VALUE AS = AGE

FROM NAV A,
           NAV B,
           NAV C

WHERE A.NAME =3D B.NAME
    AND A.NAME =3D C.NAME
    AND A.ATTRIBUTE =3D 'Weight'
    AND B.ATTRIBUTE =3D 'Height'
    AND C.ATTRIBUTE =3D 'Age'


The only problem = when I create a=20 view with the above select statement is that when there are no entries = for the=20 field name "AGE" (in the case of David), then the row does not get = displayed.=20 What's the way out in Postgresql? I know the way it is addressed in = Oracle but=20 it doesn't seem to work in Postgresql.

Thanks.

Abhi



--
Sent via pgsql-sql mailing list=20 (pgsql-sql@postgresql.org)
To make changes to your=20 = subscription:
http://www.postgresql.org/mailpref/pgsql-sql
------=_NextPart_000_002F_01CCB424.E707C450--