SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    0 Post(s)
    0 Thread(s)

    too many right joins?

    I have a client table, a contact table, and a rates table. There a field in the contacts table and the rates table that matches up with the id of the client.

    I need to list all clients, list any contacts if there are any, and list any rates if there are any. Is there any logical way to do this? Because sometimes there might be no contacts but rates will exist and vice versa. I don't know if there's any way I can group the records later in a report to present this logically.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Toronto, Canada
    63 Post(s)
    3 Thread(s)
    stay away from right joins, they are the same as left joins (except in the other direction), so you might as well join everything from left to right

    start with the table that you know has all the rows you want

    then left join the others to it, one at a time

    easy, peasy

    use COALESCE to ensure that something meaningful is returned if there are no matching rows
         , cl.address
         , coalesce(,'no contact') as contact_name
         , coalesce(rt.desc,'no rate') as rate_desc
      from clients as cl
    left outer
      join contacts as co 
        on = co.client_id
    left outer
      join rates as rt
        on = rt.client_id | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Posting Permissions

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