From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Gauthier, Dave 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. |
