Quantcast

Self-Join

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Self-Join

Abhinandan Raghavan
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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

selfjoin_example.png (22K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Self-Join

Bèrto ëd Sèra
Hi Abhinandan,

it's just the same outer join you'd do in Oracle, see:


Bèrto

On 6 December 2011 16:57, Abhinandan Raghavan <[hidden email]> wrote:
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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql




--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Self-Join

Oliveiros d'Azevedo Cristina
In reply to this post by Abhinandan Raghavan
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 -----
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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Self-Join

Scott Swank
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
<[hidden email]> 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: [hidden email]
> 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 ([hidden email])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Self-Join

Oliveiros d'Azevedo Cristina
I have not.

I've already skimmed through it.

Indeed, it is very interesting

Thanx , Scott

Best,
Oliver

----- Original Message -----
From: "Scott Swank" <[hidden email]>
To: "Oliveiros d'Azevedo Cristina" <[hidden email]>
Cc: "Abhinandan Raghavan" <[hidden email]>;
<[hidden email]>
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
<[hidden email]> 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: [hidden email]
> 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 ([hidden email])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql 


--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Self-Join

Scott Swank
There are two problems with the OTLT approach (as well as EAV). One is
laid out nicely by Tony.

The second issue is that this big, generic table hides crucial
information from the optimizer. If you cluster/order the data by the
lookup type you can at least minimize page/block reads and improve
data caching rates (in that common types are clustered together and
hence cached together), but you still prevent simple full table scans
of low cardinality sets. You make more involved cardinality
computations more difficult or even impossible for the optimizer to
resolve.

And every, literally every, optimizer mistake goes back to
insufficient information about data cardinality.

Scott

On Tue, Dec 6, 2011 at 9:32 AM, Oliveiros d'Azevedo Cristina
<[hidden email]> wrote:

> I have not.
>
> I've already skimmed through it.
>
> Indeed, it is very interesting
>
> Thanx , Scott
>
> Best,
> Oliver
>
> ----- Original Message ----- From: "Scott Swank" <[hidden email]>
> To: "Oliveiros d'Azevedo Cristina" <[hidden email]>
> Cc: "Abhinandan Raghavan" <[hidden email]>;
> <[hidden email]>
> 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
> <[hidden email]> 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: [hidden email]
>> 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 ([hidden email])
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
>
> --
> Sent via pgsql-sql mailing list ([hidden email])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Self-Join

Bèrto ëd Sèra
In reply to this post by Abhinandan Raghavan
Hi Abhinandan,

I suppose you mean this:

CREATE TABLE nav (
name varchar NOT NULL,
attribute text NOT NULL,
value numeric );
ALTER TABLE ONLY nav ADD CONSTRAINT nav_pkey PRIMARY KEY (name, attribute);

insert into nav values ('James','Weight',70);
insert into nav values ('James','Height',165);
insert into nav values ('James','Age',22);
insert into nav values ('David','Weight',75);
insert into nav values ('David','Height',180);

So we are at least sure we do not have duplicates.

Now,

SELECT DISTINCT n.name as Name FROM nav as n ORDER BY 1 DESC; will give us the base name list

If you could trust all values to be there, you'd do something like:
SELECT 
   n1.name as name,
   n2.weight as weight,
   n3.age as age
FROM
   (SELECT DISTINCT name as Name FROM nav as n ORDER BY 1 DESC) as n1,
   (SELECT name as Name, value as weight FROM nav as n WHERE attribute='Weight' ) as n2,
   (SELECT name as Name, value as age FROM nav as n WHERE attribute='Age' ) as n3
WHERE
   n1.name = n2.name AND

Since "Age" may be missing, you need to make an OUTER join for it:

SELECT 
   n1.name as name,
   n2.height as height,
   n3.weight as weight,
   n4.age as age
FROM
   (SELECT DISTINCT name as Name FROM nav as n ORDER BY 1 DESC) as n1,
   (SELECT name as Name, value as height FROM nav as n WHERE attribute='Height' ) as n2,
   (SELECT name as Name, value as weight FROM nav as n WHERE attribute='Weight' ) as n3
   LEFT OUTER JOIN (SELECT name as Name, value as age FROM nav as n WHERE attribute='Age' ) as n4
   ON n3.name = n4.name
WHERE
   n1.name = n2.name AND

I find this data design terrible, but I'm sure you have no more love for it then I do :) It looks like you just inherited from someone else :)

Bèrto

On 6 December 2011 16:57, Abhinandan Raghavan <[hidden email]> wrote:
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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql




--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
Loading...