SELECT DISTINCT in multiple colums

alski · #1 (**permalink**) 07-16-04, 20:10

Hi All,

In Table1 I have the following columns:

primary
RecordID.....Surname1.....Surname2....Surname3
...101...........Adams.........Ackman.......Adams
...102...........Barnes..........Adams........Bake r
...103...........Charles..........Kemp .......Tomas

I would like the following result:
Ackman
Adams
Andrews
Baker
Barnes
Charles
Kemp
Tomas

Note there are no duplicate of 'Adams'.

I can't work out the correct "SELECT DISTINCT" query

Can anyone help??

Cheer,
Alski

r937 · #2 (**permalink**) 07-16-04, 22:33

Code:

select Surname1
  from Table1
union
select Surname2
  from Table1
union
select Surname3
  from Table1

duplicates removed automatically

alski · #3 (**permalink**) 07-17-04, 01:33

Cheers Rudy - that worked so well (you don't know how many hours you've just saved me!).

After reading up on UNION's I see now that they effectively remove duplicates whereas a JOIN wouldn't - does that sound about right?

Alski