Double Join MySQL Query

Here is my query

SELECT DISTINCT variants.item_number, variants.description, variants.size,
SUM(IFNULL(order_line.quantity, 0)) as purchased_2012,
SUM(IF(inventory_line.iid = '4999', inventory_line.quantity, 0)) as end_2011,
SUM(IF(inventory_line.iid = '5000', inventory_line.quantity, 0)) as instock_now
FROM variants
LEFT JOIN inventory_line on inventory_line.item_number = variants.item_number
JOIN order_line ON order_line.item_number = variants.item_number
WHERE order_line.oid IN (50000,50001,50002)
GROUP BY variants.item_number
ORDER BY variants.item_number ASC

I am trying to show a full left join on the variants table so that all records are listed for variants then the results for each other record even if null.  But I keep getting only records that fill all values in query.
LVL 2
hexfusionAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
tim_csConnect With a Mentor Commented:
You are not doing a left join on the Order_Line table so you are not going to get back every line in the variants table if there is no match to Order_Line and even then you are only going to get back the records that match the OID.  Maybe this is what you're wanting?

SELECT DISTINCT variants.item_number, variants.description, variants.size, 
SUM(IFNULL(order_line.quantity, 0)) as purchased_2012, 
SUM(IF(inventory_line.iid = '4999', inventory_line.quantity, 0)) as end_2011, 
SUM(IF(inventory_line.iid = '5000', inventory_line.quantity, 0)) as instock_now
FROM variants
LEFT JOIN inventory_line on inventory_line.item_number = variants.item_number
LEFT JOIN order_line ON order_line.item_number = variants.item_number AND order_line.oid IN (50000,50001,50002)
GROUP BY variants.item_number
ORDER BY variants.item_number ASC

Open in new window

0
 
santhimurthydCommented:
In your SQL, you are enbling Inner join with the tables

FROM variants
JOIN order_line ON order_line.item_number = variants.item_number

and alos have an where condition from the above cartesion product (Cross result sets)
where order_line.oid IN (50000,50001,50002)

check whether is ther any record set in this condition which don't have record in the Left outer Join table inventory_line on the filed item_number
LEFT JOIN inventory_line on inventory_line.item_number = variants.item_number

Try by running the queries separtley

SELECT DISTINCT variants.item_number, variants.description, variants.size, 
SUM(IFNULL(order_line.quantity, 0)) as purchased_2012, 
FROM variants
JOIN order_line ON order_line.item_number = variants.item_number
WHERE order_line.oid IN (50000,50001,50002)
GROUP BY variants.item_number
ORDER BY variants.item_number ASC

Open in new window



SELECT DISTINCT inventory_line.item_number from inventory_line

Open in new window

0
 
hexfusionAuthor Commented:
thanks a lot just needed some fresh eyes
0
 
hexfusionAuthor Commented:
hmm recordset for SUM(IFNULL(order_line.quantity, 0)) as purchased_2012, is now doubled?
0
 
hexfusionAuthor Commented:
SUM(DISTINCT IFNULL(order_line.quantity, 0)) as purchased_2012,  solved double record issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.