Need sql to pull data from terribly architected table

From: "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Need sql to pull data from terribly architected table
Date: 2012-10-23 18:52:52
Message-ID: 0AD01C53605506449BA127FB8B99E5E13E0F8E70@FMSMSX105.amr.corp.intel.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 !

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Curvey 2012-10-23 19:06:00 Re: Need sql to pull data from terribly architected table
Previous Message Kevin Grittner 2012-10-23 18:18:23 Re: database corruption questions