Results 1 to 2 of 2

Thread: join...

  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: join...

    I have a left join between table1 and table2:

    select table1.*, table2.contact, table2.telephone from table1
    LEFT JOIN table2 On table1.CUSTOMER = table2.customer

    ... problem is table2 may occasionally have more than 1 entry for a customer (where the repcode is different in each record for the customer)... Instead of the join returning the transaction twice, I want it to only show 1 of the contact details (doesnt matter which record)... How could i do this???

    Thanks in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    doesn't matter which one? oh boy, let's pick the one with the lowest telephone number!!
    Code:
    select table1.*
         , table2.contact
         , table2.telephone 
      from table1 
    LEFT 
      JOIN table2 as T2
        On T2.customer = table1.CUSTOMER
       and T2.telephone =
           ( select min(telephone)
               from table2
              where customer = table1.CUSTOMER )
    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
  •