Thread: Self-Join

Self-Join

From

Abhinandan Raghavan

Date:

06 December 2011, 12:57:41

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

Attachment

selfjoin_example.png

Re: Self-Join

From

Bèrto ëd Sèra

Date:

06 December 2011, 13:04:33

Hi Abhinandan,

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

http://www.postgresql.org/docs/9.1/static/tutorial-join.html

Bèrto

On 6 December 2011 16:57, Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch> 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 (pgsql-sql@postgresql.org)
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.

Re: Self-Join

From

"Oliveiros d'Azevedo Cristina"

Date:

06 December 2011, 13:40:10

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

Re: Self-Join

From

Scott Swank

Date:

06 December 2011, 16:18:23

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

Re: Self-Join

From

"Oliveiros d'Azevedo Cristina"

Date:

06 December 2011, 16:32:25

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

Re: Self-Join

From

Scott Swank

Date:

06 December 2011, 17:15:02

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
<oliveiros.cristina@marktest.pt> wrote:
> 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

Re: Self-Join

From

Bèrto ëd Sèra

Date:

06 December 2011, 17:58:53

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

n1.name = n3.name;

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

n1.name = n3.name;

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 <Abhinandan.Raghavan@unige.ch> 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 (pgsql-sql@postgresql.org)
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.