Re: 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