?
Solved

Double Join MySQL Query

Posted on 2013-02-01
5
Medium Priority
?
417 Views
Last Modified: 2013-02-01
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.
0
Comment
Question by:hexfusion
  • 3
5 Comments
 
LVL 15

Accepted Solution

by:
tim_cs earned 2000 total points
ID: 38844759
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
 
LVL 15

Expert Comment

by:santhimurthyd
ID: 38844790
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
 
LVL 2

Author Closing Comment

by:hexfusion
ID: 38844853
thanks a lot just needed some fresh eyes
0
 
LVL 2

Author Comment

by:hexfusion
ID: 38844876
hmm recordset for SUM(IFNULL(order_line.quantity, 0)) as purchased_2012, is now doubled?
0
 
LVL 2

Author Comment

by:hexfusion
ID: 38844966
SUM(DISTINCT IFNULL(order_line.quantity, 0)) as purchased_2012,  solved double record issue.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month12 days, 15 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question