Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    24

    Unanswered: SELECT DISTINCT in multiple colums

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select Surname1
      from Table1
    union
    select Surname2
      from Table1
    union
    select Surname3
      from Table1
    duplicates removed automatically

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2002
    Posts
    24
    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

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by alski
    After reading up on UNION's I see now that they effectively remove duplicates whereas a JOIN wouldn't - does that sound about right?
    As a side note, your original set did not contain any duplicates.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by r123456
    As a side note, your original set did not contain any duplicates.
    Adams -- 101 Surname3 and 102 Surname2

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •