> 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