Results 1 to 2 of 2
Thread: too many right joins?
Apr 1, 2005, 12:39 #1
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.
Apr 1, 2005, 16:43 #2
- 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
use COALESCE to ensure that something meaningful is returned if there are no matching rowsCode:
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