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.
Comments
Post a Comment