From: | Bèrto ëd Sèra <berto(dot)d(dot)sera(at)gmail(dot)com> |
---|---|
To: | Abhinandan Raghavan <Abhinandan(dot)Raghavan(at)unige(dot)ch> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Self-Join |
Date: | 2011-12-06 18:58:38 |
Message-ID: | CAKwGa_8o5f4sez-meFCqxyR3FtMQigzwaQ-adZ32nXmuXe3xoA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)Raghavan(at)unige(dot)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(at)postgresql(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | feng.zhou | 2011-12-07 11:27:14 | No response from the backend |
Previous Message | Scott Swank | 2011-12-06 18:14:46 | Re: Self-Join |