Re: Self-Join
| От | Bèrto ëd Sèra |
|---|---|
| Тема | Re: Self-Join |
| Дата | |
| Msg-id | CAKwGa_8o5f4sez-meFCqxyR3FtMQigzwaQ-adZ32nXmuXe3xoA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Self-Join (Abhinandan Raghavan <Abhinandan.Raghavan@unige.ch>) |
| Список | 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);
--
==============================
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.
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
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
WHERE
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:A.VALUE AS WEIGHT,B.VALUE AS HEIGHT,C.VALUE AS AGEFROM NAV A,NAV B,NAV CAND 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.
В списке pgsql-sql по дате отправления: