Re: Self-Join

I have not.

I've already skimmed through it.

Indeed, it is very interesting

Thanx , Scott

Best,
Oliver

----- Original Message -----
From: "Scott Swank" <scott(dot)swank(at)gmail(dot)com>
To: "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
Cc: "Abhinandan Raghavan" <Abhinandan(dot)Raghavan(at)unige(dot)ch>;
<pgsql-sql(at)postgresql(dot)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(dot)cristina(at)marktest(dot)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(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql