Distinct column from union of multiple table and multiple columns

Question

question

Graham 2 asked Mar 25, '10

Distinct column from union of multiple table and multiple columns

I have 3 tables with email address and contact details and I want to produce one list from all 3 with no duplicate email addresses with a preference to keeping the record from the first database.

I've used union distinct but that only gives me distinct rows not distinct email addresses i.e.

table1                    
fred@ibm.com, fred, Y                    
john@ibm.com, john, Y                    
                    
table2                    
fred@ibm.com, fred, N                    
john@ibm.com, jonny, N                    
                    
table3                    
bob@ibm.com, bob, N

just using a union distinct

select EML, FN, 'Y' Z from table1                    
union distinct                     
select EML, FN, 'N' Z from table2                    
union distinct                     
select EML, FN, 'N' Z from table3                    
order by Z DESC

gives me

fred@ibm.com, fred, Y                    
john@ibm.com, john, Y                    
fred@ibm.com, fred, N                    
john@ibm.com, jonny, N                    
bob@ibm.com, bob, N

but I only want distinct email addresses not rows with a preference to keeping the record on table1 as below

fred@ibm.com, fred, Y                    
john@ibm.com, john, Y                    
bob@ibm.com, bob, N

Any help would be appreciated

aggregates union

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-03-25T19:39:49Z

Squirrel 1 answered Mar 25, '10

select EML, FN, 'Y' Z from table1 t1            
union distinct             
select EML, FN, 'N' Z from table2 t2            
       where not exists (select * from table1 x where x.EML = t2.EML and x.FN = t2.FN)            
union distinct             
select EML, FN, 'N' Z from table3            
       where not exists (select * from table1 x where x.EML = t3.EML and x.FN = t3.FN)            
       and   not exists (select * from table2 x where x.EML = t3.EML and x.FN = t3.FN)            
order by Z DESC

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-03-29T04:54:02Z

Graham 2 answered Mar 29, '10

Thanks, after much head scratching I got it to work using a sub-select

            
            
select s.EML , max(s.Z) max(s.FN) from             
(            
select EML, FN, 'Y' Z from table1            
union distinct             
select EML, FN, 'N' Z from table2            
union distinct             
select EML, FN, 'N' Z from table3             
order by Z DESC            
)            
as s group by s.EML

It's the max on the Z (Y/N) that gives the preference on the first table.

Hope this helps someone else.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question