From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | "'Gauthier, Dave'" <dave(dot)gauthier(at)intel(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Need sql to pull data from terribly architected table |
Date: | 2012-10-23 19:08:40 |
Message-ID: | 015501cdb151$d0425d90$70c718b0$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Gauthier, Dave
Sent: Tuesday, October 23, 2012 2:53 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Need sql to pull data from terribly architected table
Here's the deal...
Instead of architecting and loading a table like...
create teble foo (col1 text, col2 text, col3 text, col4 text, col5 text);
insert into foo (col1,col2,col3,col4,col5) values
('c1',null,'c3','c4',null);
They did this instead...
create table foo (property text, value text);
insert into foo (property, value) values ('col1','c1'), ('col3','c3'),
('col4','c4');
Notice how "col2" and "col5" were simply left out of the table in the 2nd
model to indicate null.
The question is how to do this model 1 query for model 2...
select col1,col2 from foo where col4='c4' and col5 <> 'xxx';
I know I have to use outer joins to deal with the potential of nulls. But I
don't know how to construct this. I won't list my failed attempts (so as
not to embarass myself :-))
Thanks in Advance !
WITH remap (
SELECT id_field_not_shown_above, v1, v2, v3, v4, v5
FROM (SELECT id_field_not_shown_above FROM foo) master
LEFT JOIN (SELECT id_field_not_shown_above, v1 FROM foo WHERE property =
'col1') r1 USING (id_field_not_shown_above)
LEFT JOIN (SELECT id_field_not_shown_above, v2 FROM foo WHERE property =
'col2') r2 USING (id_field_not_shown_above)
LEFT JOIN (SELECT id_field_not_shown_above, v3 FROM foo WHERE property =
'col3') r3 USING (id_field_not_shown_above)
LEFT JOIN (SELECT id_field_not_shown_above, v4 FROM foo WHERE property =
'col4') r4 USING (id_field_not_shown_above)
LEFT JOIN (SELECT id_field_not_shown_above, v5 FROM foo WHERE property =
'col5') r5 USING (id_field_not_shown_above)
)
SELCET * FROM remap
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma | 2012-10-23 19:21:38 | Re: Need sql to pull data from terribly architected table |
Previous Message | Chris Curvey | 2012-10-23 19:06:00 | Re: Need sql to pull data from terribly architected table |