Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Double Join MySQL Query

Posted on 2013-02-01
5
Medium Priority
?
413 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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…

604 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