Re: Self-Join
От | Oliveiros d'Azevedo Cristina |
---|---|
Тема | Re: Self-Join |
Дата | |
Msg-id | 98AAAADF76F646818830175C98637375@marktestcr.marktest.pt обсуждение исходный текст |
Ответ на | Self-Join (Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch>) |
Ответы |
Re: Self-Join
(Scott Swank <scott.swank@gmail.com>)
|
Список | pgsql-sql |
I have not. I've already skimmed through it. Indeed, it is very interesting Thanx , Scott Best, Oliver ----- Original Message ----- From: "Scott Swank" <scott.swank@gmail.com> To: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt> Cc: "Abhinandan Raghavan" <Abhinandan.Raghavan@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.cristina@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
В списке pgsql-sql по дате отправления: