pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelf Join?
15+ messages / 10 participants
[nested] [flat]
* Self Join?
@ 2002-10-04 14:05 bens_nospam@benjamindsmith.com
0 siblings, 0 replies; 15+ messages in thread
From: bens_nospam@benjamindsmith.com @ 2002-10-04 14:05 UTC (permalink / raw)
To: pgsql-sql
I'm having difficulty coming up with the right join to get my results.
I'm using PostgreSQL 7.2.x
I'm looking for a "most likely match" result. Assume you have a table with two fields, field 1 is a serial key (unique) and field 2 is varchar.
Assume that you have the following entries in the table:
recordid val
1, 'a'
2, 'b'
3, 'ab'
And I want to match the strings "a" and "b", but not necessarily "ab", and disregard an additional "c", and organize the result so that the records that best match are at the top. Sample output might be:
count recordid
2 3
1 1
1 2
Record #3, containing both "a" and "b" has two count, records 1 and 2 having only one of "a" or "b" have a count of 1.
The closest that I've come so far is from a query like
select id from table where lower(val) like lower('%a%') UNION ALL select id from table where lower(val) like lower('%b%') UNION ALL select id from table where lower(val) like lower(%c%');
What this gives me is
id
1
3
2
3
which is somewhat close, but then requires me to loop thru a potentially large number of results to get the requested output.
Anybody else up to this one?
-Ben
^ permalink raw reply [nested|flat] 15+ messages in thread
* self join
@ 2011-05-14 22:09 Seb <spluque@gmail.com>
2011-05-14 22:39 ` Re: self join Ian Lawrence Barwick <barwick@gmail.com>
2011-05-15 02:36 ` Re: self join Jasen Betts <jasen@xnet.co.nz>
0 siblings, 2 replies; 15+ messages in thread
From: Seb @ 2011-05-14 22:09 UTC (permalink / raw)
To: pgsql-sql
Hi,
This probably reflects my confusion with how self joins work.
Suppose we have this table:
=# SELECT * FROM tmp;
a | b
---+---
1 | 2
2 | 3
4 | 5
(3 rows)
If I want to get a table with records where none of the values in column
b are found in column a, I thought this should do it:
=# SELECT * FROM tmp t1, tmp t2 WHERE t2.b <> t1.a;
a | b | a | b
---+---+---+---
1 | 2 | 1 | 2
1 | 2 | 2 | 3
1 | 2 | 4 | 5
2 | 3 | 2 | 3
2 | 3 | 4 | 5
4 | 5 | 1 | 2
4 | 5 | 2 | 3
4 | 5 | 4 | 5
(8 rows)
I need to get:
a | b | a | b
---+---+---+---
1 | 2 | 1 | 2
4 | 5 | 4 | 5
Or just:
a | b
---+---
1 | 2
4 | 5
--
Seb
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: self join
2011-05-14 22:09 self join Seb <spluque@gmail.com>
@ 2011-05-14 22:39 ` Ian Lawrence Barwick <barwick@gmail.com>
2011-05-14 22:49 ` Re: self join Seb <spluque@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: Ian Lawrence Barwick @ 2011-05-14 22:39 UTC (permalink / raw)
To: Seb <spluque@gmail.com>; +Cc: pgsql-sql
Hi
2011/5/15 Seb <spluque@gmail.com>:
> Hi,
>
> This probably reflects my confusion with how self joins work.
>
> Suppose we have this table:
>
> =# SELECT * FROM tmp;
> a | b
> ---+---
> 1 | 2
> 2 | 3
> 4 | 5
> (3 rows)
>
> If I want to get a table with records where none of the values in column
> b are found in column a, I thought this should do it:
>
> =# SELECT * FROM tmp t1, tmp t2 WHERE t2.b <> t1.a;
> a | b | a | b
> ---+---+---+---
> 1 | 2 | 1 | 2
> 1 | 2 | 2 | 3
> 1 | 2 | 4 | 5
> 2 | 3 | 2 | 3
> 2 | 3 | 4 | 5
> 4 | 5 | 1 | 2
> 4 | 5 | 2 | 3
> 4 | 5 | 4 | 5
> (8 rows)
>
> I need to get:
>
> a | b | a | b
> ---+---+---+---
> 1 | 2 | 1 | 2
> 4 | 5 | 4 | 5
>
> Or just:
>
> a | b
> ---+---
> 1 | 2
> 4 | 5
Your query doesn't have an explicit join and is producing a cartesian result.
I don't think a self- join will work here; a subquery should produce the
result you're after:
SELECT *
FROM tmp t1
WHERE NOT EXISTS(SELECT TRUE FROM tmp t2 WHERE t2.b=t1.a);
HTH
Ian Lawrence Barwick
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: self join
2011-05-14 22:09 self join Seb <spluque@gmail.com>
2011-05-14 22:39 ` Re: self join Ian Lawrence Barwick <barwick@gmail.com>
@ 2011-05-14 22:49 ` Seb <spluque@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Seb @ 2011-05-14 22:49 UTC (permalink / raw)
To: pgsql-sql
On Sun, 15 May 2011 07:39:06 +0900,
Ian Lawrence Barwick <barwick@gmail.com> wrote:
[...]
> Your query doesn't have an explicit join and is producing a cartesian
> result.
> I don't think a self- join will work here; a subquery should produce
> the result you're after:
> SELECT * FROM tmp t1 WHERE NOT EXISTS(SELECT TRUE FROM tmp t2 WHERE
> t2.b=t1.a);
This produces exactly the result I'm after. I'll need to understand the
EXISTS statement there in more detail.
Thanks!
--
Seb
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: self join
2011-05-14 22:09 self join Seb <spluque@gmail.com>
@ 2011-05-15 02:36 ` Jasen Betts <jasen@xnet.co.nz>
2011-05-15 08:04 ` Re: self join Harald Fuchs <hari.fuchs@gmail.com>
2011-05-16 15:50 ` Re: self join Steve Crawford <scrawford@pinpointresearch.com>
1 sibling, 2 replies; 15+ messages in thread
From: Jasen Betts @ 2011-05-15 02:36 UTC (permalink / raw)
To: pgsql-sql
On 2011-05-14, Seb <spluque@gmail.com> wrote:
> Hi,
>
> This probably reflects my confusion with how self joins work.
>
> Suppose we have this table:
> If I want to get a table with records where none of the values in column
> b are found in column a, I thought this should do it:
use the "NOT IN" operator with a subquery to retch the disallowed
values.
select * from tmp where a NOT IN (select b from tmp);
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: self join
2011-05-14 22:09 self join Seb <spluque@gmail.com>
2011-05-15 02:36 ` Re: self join Jasen Betts <jasen@xnet.co.nz>
@ 2011-05-15 08:04 ` Harald Fuchs <hari.fuchs@gmail.com>
1 sibling, 0 replies; 15+ messages in thread
From: Harald Fuchs @ 2011-05-15 08:04 UTC (permalink / raw)
To: pgsql-sql
In article <iqne7u$ssa$2@reversiblemaps.ath.cx>,
Jasen Betts <jasen@xnet.co.nz> writes:
> On 2011-05-14, Seb <spluque@gmail.com> wrote:
>> Hi,
>>
>> This probably reflects my confusion with how self joins work.
>>
>> Suppose we have this table:
>> If I want to get a table with records where none of the values in column
>> b are found in column a, I thought this should do it:
> use the "NOT IN" operator with a subquery to retch the disallowed
> values.
> select * from tmp where a NOT IN (select b from tmp);
The third way is an OUTER JOIN:
SELECT t1.a, t1.b
FROM tmp t1
LEFT JOIN tmp t2 ON t2.b = t1.a
WHERE t2.b IS NULL;
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: self join
2011-05-14 22:09 self join Seb <spluque@gmail.com>
2011-05-15 02:36 ` Re: self join Jasen Betts <jasen@xnet.co.nz>
@ 2011-05-16 15:50 ` Steve Crawford <scrawford@pinpointresearch.com>
2011-05-17 09:07 ` Re: self join Jasen Betts <jasen@xnet.co.nz>
1 sibling, 1 reply; 15+ messages in thread
From: Steve Crawford @ 2011-05-16 15:50 UTC (permalink / raw)
To: Jasen Betts <jasen@xnet.co.nz>; +Cc: pgsql-sql
On 05/14/2011 07:36 PM, Jasen Betts wrote:
>
> use the "NOT IN" operator with a subquery to retch the disallowed
> values....
Hmmm, "retch" as a synonym for "output"? I've seen more than one case
where that is an appropriate description. :)
Cheers,
Steve
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: self join
2011-05-14 22:09 self join Seb <spluque@gmail.com>
2011-05-15 02:36 ` Re: self join Jasen Betts <jasen@xnet.co.nz>
2011-05-16 15:50 ` Re: self join Steve Crawford <scrawford@pinpointresearch.com>
@ 2011-05-17 09:07 ` Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 15+ messages in thread
From: Jasen Betts @ 2011-05-17 09:07 UTC (permalink / raw)
To: pgsql-sql
On 2011-05-16, Steve Crawford <scrawford@pinpointresearch.com> wrote:
> On 05/14/2011 07:36 PM, Jasen Betts wrote:
>>
>> use the "NOT IN" operator with a subquery to retch the disallowed
>> values....
> Hmmm, "retch" as a synonym for "output"? I've seen more than one case
> where that is an appropriate description. :)
:) was a typo for 'fetch'
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 15+ messages in thread
* Self-Join
@ 2011-12-06 13:57 Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch>
2011-12-06 14:04 ` Re: Self-Join Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-06 14:39 ` Re: Self-Join Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-12-06 18:58 ` Re: Self-Join Bèrto ëd Sèra <berto.d.sera@gmail.com>
0 siblings, 3 replies; 15+ messages in thread
From: Abhinandan Raghavan @ 2011-12-06 13:57 UTC (permalink / raw)
To: pgsql-sql
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 <http://a.name/;,
A.VALUE AS WEIGHT,
B.VALUE AS HEIGHT,
C.VALUE AS AGE
FROM NAV A,
NAV B,
NAV C
WHEREA.NAME <http://a.name/>=B.NAME <http://b.name/;
ANDA.NAME <http://a.name/>=C.NAME <http://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
Attachments:
[image/png] selfjoin_example.png (16.2K, ../../4EDE1F3F.6060807@unige.ch/3-selfjoin_example.png)
download | view image
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Self-Join
2011-12-06 13:57 Self-Join Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch>
@ 2011-12-06 14:04 ` Bèrto ëd Sèra <berto.d.sera@gmail.com>
2 siblings, 0 replies; 15+ messages in thread
From: Bèrto ëd Sèra @ 2011-12-06 14:04 UTC (permalink / raw)
To: Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch>; +Cc: pgsql-sql
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 <http://a.name/;,
> A.VALUE AS WEIGHT,
> B.VALUE AS HEIGHT,
> C.VALUE AS AGE
>
> FROM NAV A,
> NAV B,
> NAV C
>
> WHERE A.NAME <http://a.name/; = B.NAME <http://b.name/;
> AND A.NAME <http://a.name/; = C.NAME <http://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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Self-Join
2011-12-06 13:57 Self-Join Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch>
@ 2011-12-06 14:39 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-12-06 17:17 ` Re: Self-Join Scott Swank <scott.swank@gmail.com>
2 siblings, 1 reply; 15+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2011-12-06 14:39 UTC (permalink / raw)
To: Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch>; pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Self-Join
2011-12-06 13:57 Self-Join Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch>
2011-12-06 14:39 ` Re: Self-Join Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
@ 2011-12-06 17:17 ` Scott Swank <scott.swank@gmail.com>
2011-12-06 17:32 ` Re: Self-Join Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 1 reply; 15+ messages in thread
From: Scott Swank @ 2011-12-06 17:17 UTC (permalink / raw)
To: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>; +Cc: Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch>; pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Self-Join
2011-12-06 13:57 Self-Join Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch>
2011-12-06 14:39 ` Re: Self-Join Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-12-06 17:17 ` Re: Self-Join Scott Swank <scott.swank@gmail.com>
@ 2011-12-06 17:32 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-12-06 18:14 ` Re: Self-Join Scott Swank <scott.swank@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2011-12-06 17:32 UTC (permalink / raw)
To: Scott Swank <scott.swank@gmail.com>; +Cc: Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch>; 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Self-Join
2011-12-06 13:57 Self-Join Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch>
2011-12-06 14:39 ` Re: Self-Join Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-12-06 17:17 ` Re: Self-Join Scott Swank <scott.swank@gmail.com>
2011-12-06 17:32 ` Re: Self-Join Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
@ 2011-12-06 18:14 ` Scott Swank <scott.swank@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Scott Swank @ 2011-12-06 18:14 UTC (permalink / raw)
To: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>; +Cc: Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch>; pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Self-Join
2011-12-06 13:57 Self-Join Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch>
@ 2011-12-06 18:58 ` Bèrto ëd Sèra <berto.d.sera@gmail.com>
2 siblings, 0 replies; 15+ messages in thread
From: Bèrto ëd Sèra @ 2011-12-06 18:58 UTC (permalink / raw)
To: Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch>; +Cc: pgsql-sql
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 <http://a.name/;,
> A.VALUE AS WEIGHT,
> B.VALUE AS HEIGHT,
> C.VALUE AS AGE
>
> FROM NAV A,
> NAV B,
> NAV C
>
> WHERE A.NAME <http://a.name/; = B.NAME <http://b.name/;
> AND A.NAME <http://a.name/; = C.NAME <http://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.
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2011-12-06 18:58 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-04 14:05 Self Join? bens_nospam@benjamindsmith.com
2011-05-14 22:09 self join Seb <spluque@gmail.com>
2011-05-14 22:39 ` Re: self join Ian Lawrence Barwick <barwick@gmail.com>
2011-05-14 22:49 ` Re: self join Seb <spluque@gmail.com>
2011-05-15 02:36 ` Re: self join Jasen Betts <jasen@xnet.co.nz>
2011-05-15 08:04 ` Re: self join Harald Fuchs <hari.fuchs@gmail.com>
2011-05-16 15:50 ` Re: self join Steve Crawford <scrawford@pinpointresearch.com>
2011-05-17 09:07 ` Re: self join Jasen Betts <jasen@xnet.co.nz>
2011-12-06 13:57 Self-Join Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch>
2011-12-06 14:04 ` Re: Self-Join Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-06 14:39 ` Re: Self-Join Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-12-06 17:17 ` Re: Self-Join Scott Swank <scott.swank@gmail.com>
2011-12-06 17:32 ` Re: Self-Join Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-12-06 18:14 ` Re: Self-Join Scott Swank <scott.swank@gmail.com>
2011-12-06 18:58 ` Re: Self-Join Bèrto ëd Sèra <berto.d.sera@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox