charlie charlie - 6 months ago 5
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
a.retail
and
b.cost
if the row exists in
call_costs_custom
but if the row does not exist, i want to show just
a.retail
using the
WHERE
clauses for
a. (call_costs)

Answer

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';
Comments