CODEWITHSUNDEEP
mysql
user3308219
user3308219

Reputation: 157

SQL - Left join to two tables returns nothing

The query I have is as follows.

SELECT A.ItemID, A.MSRP, A.SalesPrice, A.Cost, A.DiscountPercent,
       B.QuantityOnHand, B.QuantityAvailable, C.OrderRate, C.OrderQuantity
FROM item_info A
LEFT JOIN inventory_status B
   ON A.ItemID = B.ItemID
LEFT JOIN order_line C
   ON A.ItemID = C.OrderLineItemID
WHERE A.Name LIKE ?
AND C.OrderRate != 0
AND C.OrderQuantity != 0
ORDER BY C.OrderRate ASC, C.OrderQuantity ASC
LIMIT 1

Basically, the idea is to run a search for some item and retrieve its basic information, quantities in stock, and historical data. Prior to modifying this query in order to get the historical data, it worked perfectly and simply gave me null for both QuantityOnHand and QuantityAvailable in the event that it had no entry in the inventory_status table, while the rest of its information would return normally. However, trying to join to the third table no longer does this. Now, nothing is returned. Is there any way to modify this so that I get null for OrderRate and OrderQuantity in the event that no record for the item retrieved exists in the order_line table?

Upvotes: 1

Views: 49

Answers (2)

CptMisery
CptMisery

Reputation: 614

Change this part

LEFT JOIN order_line C
ON A.ItemID = C.OrderLineItemID
WHERE A.Name LIKE ?
AND C.OrderRate != 0
AND C.OrderQuantity != 0

to this

LEFT JOIN order_line C
ON A.ItemID = C.OrderLineItemID
AND C.OrderRate != 0
AND C.OrderQuantity != 0
WHERE A.Name LIKE ?

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You have to move the predicates

C.OrderRate != 0 AND 
C.OrderQuantity != 0

from WHERE to ON clause:

SELECT A.ItemID, A.MSRP, A.SalesPrice, A.Cost, A.DiscountPercent, 
       B.QuantityOnHand, B.QuantityAvailable, C.OrderRate, C.OrderQuantity
FROM item_info A
LEFT JOIN inventory_status B
   ON A.ItemID = B.ItemID
LEFT JOIN order_line C
   ON A.ItemID = C.OrderLineItemID AND 
      C.OrderRate != 0 AND 
      C.OrderQuantity != 0
WHERE A.Name LIKE ?
ORDER BY C.OrderRate ASC, C.OrderQuantity ASC
LIMIT 1

Otherwise, the LEFT JOIN operation becomes an INNER JOIN.

Upvotes: 4

Related Questions