I have not.
I've already skimmed through it.
Indeed, it is very interesting
Thanx , Scott
Best,
Oliver
----- Original Message -----
From: "Scott Swank" <scott.sw...@gmail.com>
To: "Oliveiros d'Azevedo Cristina" <oliveiros.crist...@marktest.pt>
Cc: "Abhinandan Raghavan" <abhinandan.ragha...@unige.ch>;
<pgsql-sql@postgresql.org>
Sent: Tuesday, December 06, 2011 5:17 PM
Subject: Re: [SQL] Self-Join
Have you read Tony Andrew's 2004 piece on this approach? It is a classic.
http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html
Scott
On Tue, Dec 6, 2011 at 6:39 AM, Oliveiros d'Azevedo Cristina
<oliveiros.crist...@marktest.pt> wrote:
Howdy, Abhinandan,
A quick and dirty solution might be this :
SELECT *
FROM
(
SELECT a.name,MAX(b.value) as height
FROM original a
LEFT JOIN original b
ON a.name = b.name
AND b.attribute = '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 = b.name
AND b.attribute = 'Weight'
GROUP BY a.name
) weight
NATURAL JOIN
(
SELECT a.name,MAX(b.value) as age
FROM original a
LEFT JOIN
original
b
ON a.name = b.name
AND b.attribute = '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 -----
From: Abhinandan Raghavan
To: pgsql-sql@postgresql.org
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.
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:
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 = B.NAME
AND A.NAME = C.NAME
AND A.ATTRIBUTE = 'Weight'
AND B.ATTRIBUTE = 'Height'
AND C.ATTRIBUTE = '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
________________________________
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
