mysql - sql left join select all records from left table even if no records in rigth table -

i trying run following query :

select ifnull(sum(item_actual_qty),0) + b.item_opening_balance closing transaction_inventory  left join inventory_master b on b.item_code = a.item_code , b.company_code = a.company_code a.item_code = 2222       , a.company_code = '52889497-5b6b-403d-8f83-224e3c7759b4'       , a.trans_type_name not in ('sales order','purchase order')   , a.trans_date < '2010-04-01' ; 

how select records inventory_master if there not records in transaction_inventory ? giving null value b.item_opening_balance should give actual item opening balance master table.

putting sub query

select ifnull(sum(item_actual_qty),0) + (select item_opening_balance inventory_master item_code = a.item_code) closing transaction_inventory  a.item_code = 2222   , a.company_code = '52889497-5b6b-403d-8f83-224e3c7759b4'   , a.trans_type_name not in ('sales order','purchase order')   , a.trans_date < '2010-04-01' 

returns item opening balance inventory_master, avoiding use subquery

you need use outer join if want records 1 of tables.

select ifnull(sum(item_actual_qty),0) aa,b.item_name,a.item_code  transaction_inventory right outer join inventory_master b on b.item_code = a.item_code , b.company_code = a.company_code  a.item_code = 2222        , a.company_code = '52889497-5b6b-403d-8f83-224e3c7759b4'        , a.trans_type_name not in ('sales order','purchase order')        , a.trans_date < '2010-04-01' ; 

if reorder clause, can use left outer join:

select ifnull(sum(item_actual_qty),0) aa,b.item_name,a.item_code   inventory_master b left outer join transaction_inventory on b.item_code = a.item_code , b.company_code = a.company_code  a.item_code = 2222        , a.company_code = '52889497-5b6b-403d-8f83-224e3c7759b4'        , a.trans_type_name not in ('sales order','purchase order')        , a.trans_date < '2010-04-01' ; 

use left outer join if left table 1 have records returned, regardless of null values in right one, or right outer join if table records on right.