CODEWITHSUNDEEP
mysqlleft-join
JJT
JJT

Reputation: 91

Conditional SQL Left join

I got two table, and i would like to join them with expected result.

Table 1:ItemUOM
Itemcode     UOM        Rate
Item00123    Pkt454g    454
Item00123    Pkt252g    252 
Item00123    GM         1

Table 2:  vItemBalQty 
Itemcode     UOM        BalQty    Location
Item00123    PKT454g    200       2001
Item00123    PKT252g    150       2001

Expected Result:
Itemcode     UOM        BalQty   Location
Item00123    PKT454g    200      2001
Item00123    PKT252g    150      2001
Item00123    GM         0        2001

But my actual result with my query is not same with expected one.

select a.ItemCode,a.uom,coalesce(b.BalQty,0.00) from itemuom a
left join vItemBalQty b on  b.ItemCode = a.ItemCode and a.uom = b. uom
where a.itemcode = 'Item00123' and b.location ='2001'

Actual Result:
Itemcode     UOM        BalQty   Location
Item00123    PKT454g    200      2001
Item00123    PKT252g    150      2001

Upvotes: 0

Views: 47

Answers (1)

Nick
Nick

Reputation: 147256

You need to move the b.location = '2001' condition from the WHERE clause to the JOIN condition, otherwise it turns it into an INNER JOIN (see the paragraph about WHERE conditions in the manual) i.e.

select a.ItemCode,a.uom,coalesce(b.BalQty,0.00)
from itemuom a
left join vItemBalQty b on  b.ItemCode = a.ItemCode and a.uom = b. uom and b.location ='2001'
where a.itemcode = 'Item00123'

As @ysth put it, adding the b.location = '2001' condition to the WHERE clause means that b.location can not be NULL, which it would otherwise be for an unmatched row, thus excluding those rows from the result. By adding to the condition to the LEFT JOIN we return rows which have b.location = '2001, but still return an empty b row where that condition is not satisfied.

Upvotes: 2

Related Questions