SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    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
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    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
    Code:
    select cl.name
         , cl.address
         , coalesce(co.name,'no contact') as contact_name
         , coalesce(rt.desc,'no rate') as rate_desc
      from clients as cl
    left outer
      join contacts as co 
        on cl.id = co.client_id
    left outer
      join rates as rt
        on cl.id = rt.client_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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