Solved

Double Join MySQL Query

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
[MYSQL]: Delete is very slow 4 70
simple shopping cart database structure 5 43
mysql ide 10 40
mysql between clause 2 24
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

772 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