Solved

Double Join MySQL Query

Posted on 2013-02-01
5
398 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 500 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…

820 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