date:20111206

[SQL] Self-Join

2011-12-06 Thread 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:



SELECTA.NAME ,
 A.VALUE AS WEIGHT,
 B.VALUE AS HEIGHT,
 C.VALUE AS AGE

FROM NAV A,
   NAV B,
   NAV C

WHEREA.NAME =B.NAME 
ANDA.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: [SQL] Self-Join

2011-12-06 Thread Bèrto ëd Sèra

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
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: [SQL] Self-Join

2011-12-06 Thread Oliveiros d'Azevedo Cristina

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: [SQL] Self-Join

2011-12-06 Thread 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
 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: [SQL] Self-Join

2011-12-06 Thread 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" 

To: "Oliveiros d'Azevedo Cristina" 
Cc: "Abhinandan Raghavan" ; 


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



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

Re: [SQL] Self-Join

2011-12-06 Thread 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
 wrote:
> I have not.
>
> I've already skimmed through it.
>
> Indeed, it is very interesting
>
> Thanx , Scott
>
> Best,
> Oliver
>
> - Original Message - From: "Scott Swank" 
> To: "Oliveiros d'Azevedo Cristina" 
> Cc: "Abhinandan Raghavan" ;
> 
> 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
>  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

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

Re: [SQL] Self-Join

2011-12-06 Thread Bèrto ëd Sèra

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
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.

[SQL] Self-Join

Re: [SQL] Self-Join

Re: [SQL] Self-Join

Re: [SQL] Self-Join

Re: [SQL] Self-Join

Re: [SQL] Self-Join

Re: [SQL] Self-Join

7 matches

Site Navigation

Mail list logo

Footer information