On Mon, 18 Apr 2022 20:48:13 +0300, Binyamin Dissen
<[email protected]> wrote:
>Seems that I have been knocked off of the DB2-L listserv.
>
Sorry to hear that... Maybe re-apply because the real SQL specialists live over
there.
>I am doing a union of three queries where it is possible that the critical
>columns are in more than one of the queries.
>
>For example:
>
> select col1, col2 col3, 'source 1'
> from table1
> where something
> union
> select col1, col2, col3, 'source 2'
> from table2
> where something
> union
> select col1, col2, col3, 'source 3'
> from table3
> where something
>
>I would like a single row even if the data (col1, col2, col3) is in more than
>one of the queries, so that 'source1' is returned if in table1 and table2
>and/or table3, 'source 2' if not in table1 but in table2 (and perhaps table3)
>and 'source 3' if only in table 3.
>
To be tested:
select col1, col2, col3, min(source_tbl) as first_tbl
from (
select col1, col2, col3, 'source 1' as source_tbl
from table1
where something
union all
select col1, col2, col3, 'source 2' as source_tbl
from table2
where something
union all
select col1, col2, col3, 'source 3' as source_tbl
from table3
where something
) as cols
group by col1, col2, col3
;
This of course supposes that the source-table-identifying values you assign in
the different sub queries have the values they have in your example.
Cheers,
Jantje.
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to [email protected] with the message: INFO IBM-MAIN
