I made a simple query report where LEFT JOIN does not return all the rows from the left table ,
totally I have 571 products when using LEFT JOIN it returns only 270 products .
select
it.item_code as "Part no:Data:180",
`tabBin`.actual_qty as "Qty:Int:80"
from
`tabItem` it LEFT JOIN `tabBin` ON it.item_code = `tabBin`.item_code
where
it.item_group in ('Products')
and `tabBin`.warehouse in ('Cutting - MYCOMPANY')
I believe LEFT JOIN should return all the values from the item master even if there is no match in the Bin , those which dont match will be returned null , but here most of my products vanish and only for those items which were there in cutting warehouse at least once are getting displayed.
Moving the where clause to the join statement solved my issue but I ran into other problem , I tried adding alias to Bin table to find the quantity of the same item in the other warehouse , but when adding a second alias makes the first invalid ,
select
it.item_code as "Part no:Data:180",
cut.actual_qty as "Qty:Int:80",
frg.actual_qty as "Qty:Int:80"
from
`tabItem` it LEFT JOIN `tabBin` cut ON (cut.item_code = it.item_code and cut.warehouse in ('Cutting - MYCOMP'))
LEFT JOIN `tabBin` frg ON (frg.item_code = it.item_code and frg.warehouse in ('Forging - MYCOMP'))
where
it.item_group in ('Products')
the above query displays two columns , the qty in both the columns are from the forging warehouse .
