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