charlie charlie - 2 years ago 91
SQL Question

sql query selecting from two tables - return results from one table if there are none in the other table

i am running this SQL query:

SELECT a.retail, b.cost
from call_costs a, call_costs_custom b
WHERE a.sequence = b.parent
AND a.sequence = '15684'
AND b.customer_seq = '124'

which returns both
if the row exists in
but if the row does not exist, i want to show just
using the
clauses for
a. (call_costs)

Answer Source

You want an outer join, i.e. a join that keeps records from the first table even when there is no match in the second table. Use LEFT OUTER JOIN or short LEFT JOIN hence:

select cc.retail, ccc.cost 
from call_costs cc
left join call_costs_custom ccc on ccc.parent = cc.sequence and ccc.customer_seq = '124'
where cc.sequence = '15684';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download