Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Location
    Warsaw
    Posts
    6

    Unanswered: Joining tables with eliminating duplicates

    Hi,

    I've got an worse day or something but I'm stuck. I've got two tables:

    Table1
    -------------
    id Name
    1 Barbara
    2 John
    3 Steve

    Table2
    -------------
    id Name2
    1 Wattson
    2 Jackson
    3 Henry
    1 Smith



    I want to get all rows from table1 and appropriate to id, Name2 from table2. Joined are table1.id and table2.id. Problem is that row with id = 1 is duplicated. I tried:

    SELECT a.id, a.Name, b.Name2 FROM Table1 AS a
    LEFT OUTER JOIN Table2 AS b ON a.id = b.id

    But it doesnt work as I want - I get:

    1 Barbara Wattson
    2 John Jackson
    3 Steve Henry
    1 Barbara Smith


    Any idea ?

    --
    Marcin Domaslawski

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by MarcinD
    But it doesnt work as I want - I get:

    1 Barbara Wattson
    2 John Jackson
    3 Steve Henry
    1 Barbara Smith
    that looks okay to me

    you forgot to mention which way you actually wanted it to work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2008
    Location
    Warsaw
    Posts
    6
    Ok - I want that there wouldn't be the last row - with id = 1 -> because it's duplicated (1st and 4th). Should be only 3 rows:

    1 Barbara Wattson
    2 John Jackson
    3 Steve Henry

    It's relation table1 to table2 like 1 to 0, 1 or many but I care only about that 1st found rec. from table2 appropriate to row from table1.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, there is no such thing as "first found row"

    next best thing is the minimum or maximum value...
    Code:
    SELECT a.id
         , a.Name
         , MAX(b.Name2) as Name2
      FROM Table1 AS a
    LEFT OUTER 
      JOIN Table2 AS b 
        ON b.id = a.id
    GROUP
        BY a.id
         , a.Name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2008
    Location
    Warsaw
    Posts
    6
    Yes this is it - there isn't first found row ...
    and YES this is it ... what I wanted

    I've never used MAX in this way

    Thanks

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I think you have a bigger problem than what you are working on. What happens if you get an entry in table 2 of "1,Young"? Do you still want Watson or would you now want Young? Also, how do you know your user wouldn't want the others. Typically, you'd want to keep a persons entire name in the same table rather than trying to join it up this way. Or if you were really out to save on disk space you could go with three tables, such as:

    table1 FIRSTNAMES
    FN_ID, F_NAME
    (1,ANN
    2,BARBARA
    3,JOHN)

    tABLE2 LASTNAMES
    LN_ID, L_NAME
    (1,WATSON
    2,SMITH
    3,JACKSON)

    TABLE3 NAME
    N_ID, FN_ID, LN_ID
    (1,1,1
    2,1,3
    3,2,1)
    This last table would equate to:
    ANN WATSON
    ANN JACKSON
    BARBARA WATSON

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I forgot to mention in my earlier post. If you were going with the three table solution that I spoke of, I would suggest two unique indexes on the first and last name tables. One for the ID number as you DO NOT want to end up with what you have already(not knowing which name should be used). The other on the name itself, reason being you are attempting this solution, so as to not duplicate a name, so ensure that you do not duplicate it. Without those indexes on both tables you would just be setting yourself up for data errors down the road. If that were to happen, then Vincent McBurney or one of those other data quality people would be hunting you down.

Posting Permissions

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