hexfusion
asked on
Double Join MySQL Query
Here is my query
SELECT DISTINCT variants.item_number, variants.description, variants.size,
SUM(IFNULL(order_line.quan tity, 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.
SELECT DISTINCT variants.item_number, variants.description, variants.size,
SUM(IFNULL(order_line.quan
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks a lot just needed some fresh eyes
ASKER
hmm recordset for SUM(IFNULL(order_line.quan tity, 0)) as purchased_2012, is now doubled?
ASKER
SUM(DISTINCT IFNULL(order_line.quantity , 0)) as purchased_2012, solved double record issue.
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
Try by running the queries separtley
Open in new window
Open in new window