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

Comment

**Answer** by Squirrel 1 ·

```
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
```

**Answer** by Graham 2 ·

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.

Copyright 2019 Redgate Software.
Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges