Roberts, Jon wrote:
What I am trying to do is find the difference between two tables, one
that stores the
information in a single column, and the other which stores the same
data
in multiple
columns.
E.g.
CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5
text,
col6 text, col7 text, col8 text, col9 text, col10 text);
CREATE TABLE test2(col_data text NOT NULL, some_data text NOT NULL,
other_data text,
CONSTRAINT test2_index PRIMARY
KEY(
col_data,
some_data ));
Trying to find data set in test2.col_data that is not in test.col1 to
test.col10.
FINALLY you get to the requirements. Next time, just ask a question
like the above. You were asking how to solve a technical problem that
didn't relate to the actual business need.
Here are three ways to skin this cat.
--version 1
select col_data from test2
except
select coalesce(col1, '') || coalesce(col2, '') || coalesce(col3, '') ||
coalesce(col4, '') || coalesce(col5, '') || coalesce(col6, '') ||
coalesce(col7, '') || coalesce(col8, '') || coalesce(col9, '') ||
coalesce(col10, '')
from test
--version 2
select col_data
from test2 t2
where not exists (select null
from test t
where t2.col_data = coalesce(t.col1, '') ||
coalesce(t.col2, '') ||
coalesce(t.col3, '') ||
coalesce(t.col4, '') ||
coalesce(t.col5, '') ||
coalesce(t.col6, '') ||
coalesce(t.col7, '') ||
coalesce(t.col8, '') ||
coalesce(t.col9, '') ||
coalesce(t.col10, ''))
--version 3
select t2.col_data
from test2 t2
left join (select coalesce(col1, '') || coalesce(col2, '') ||
coalesce(col3, '') || coalesce(col4, '') ||
coalesce(col5, '') || coalesce(col6, '') ||
coalesce(col7, '') || coalesce(col8, '') ||
coalesce(col9, '') || coalesce(col10, '') as
col_data
from test) t
on t2.col_data = t.col_data
where t.col_data is null
Jon
Thanks Jon for the hints.
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
