On Mon, 18 Apr 2022 20:48:13 +0300, Binyamin Dissen <bdis...@dissensoftware.com> 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 lists...@listserv.ua.edu with the message: INFO IBM-MAIN