Solved

MySQL - Show all records from one table, even if they don't exist in another

Posted on 2016-09-15
11
60 Views
Last Modified: 2016-09-16
In the query below I am getting sales details on each product category. I want it to show a row for every category specified in the WHERE clause, however right now it is only showing data if there are sales in the vCustomerInvoiceDetail table in that category. How do I get it to display every category with 0's or NULL in the columns where there's no data from the other tables?

SELECT
 cu.CustID,
 cu.CustomerName,
 c.CatID, 
 c.CategoryDescription as Category, 
IFNULL(SUM(CASE WHEN InvoiceDate between '2015-1-1' and '2015-12-31' THEN PubProfit ELSE 0 END),0) as LastYear,
IFNULL(SUM(CASE WHEN InvoiceDate between '2016-1-1' and '2016-12-31' THEN PubProfit ELSE 0 END),0) as ThisYear,
IFNULL(CASE WHEN 
	SUM(CASE WHEN InvoiceDate between '2015-1-1' and '2015-12-31' THEN PubProfit ELSE 0 END) > c.AvgTop150 THEN
    	SUM(CASE WHEN InvoiceDate between '2015-1-1' and '2015-12-31' THEN PubProfit ELSE 0 END) ELSE c.AvgTop150 END,0) as DefaultGoal,
c.AvgTop150,
r.CatGoal,
r.CurrentRank,
r.RankTimeStamp
FROM `vCustomerInvoiceDetail` i
Left Outer Join Categories c on i.ItemCategory = c.CatID
Left Outer Join GoalRankings r on i.ItemCategory = r.CatID AND i.CustID = r.CustID
Left Outer Join vCustomers cu on i.CustID = cu.CustID
Where i.CustID = $cid
and (
    c.CatID = '10' or
     c.CatID = '75' or
     c.CatID = '90' or
     c.CatID = '30' or
     c.CatID = '25' or
     c.CatID = '20' or
     c.CatID = '40'
    )
Group by Category

Open in new window

0
Comment
Question by:dannyg280
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41800025
>>How do I get it to display every category with 0's or NULL in the columns where there's no data from the other tables?

you need to select from table: Categories first and then left join with table: vCustomerInvoiceDetail
0
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41800027
like:

...
FROM  Categories c
Left Outer Join  `vCustomerInvoiceDetail` i on  c.CatID = i.ItemCategory
..

Open in new window

0
 

Author Comment

by:dannyg280
ID: 41800100
Thank you Ryan, I did as you suggested and I am still getting the same results... I'm thinking it has to do with the CASE or SUM statements.. although I'm not sure...  I saw the answer to this post: http://stackoverflow.com/questions/14967160/left-join-sum-and-count-group-by

I'm thinking that may be my issue... but I can't seem to get the syntax right to work with my query.
0
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.

 
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 41800117
The problem is that you're selecting from vCustomerInvoiceDetail, so your results will be limited to records that exist in that table.
SELECT ...
FROM `vCustomerInvoiceDetail`

One way to resolve this would be to switch the order of your joins.  You also need to add a NULL condition to your WHERE clause to allow cases where there are no records in vCustomerInvoiceDetail:  
SELECT .... 
FROM Categories c
LEFT OUTER JOIN `vCustomerInvoiceDetail` i ON i.ItemCategory = c.CatID
...
WHERE (i.CustID = $cid OR i.CustID IS NULL) AND ...

Open in new window

0
 

Author Comment

by:dannyg280
ID: 41800150
Thank you. I am still having the same issue after applying the fixes suggested. I have stripped the query down a bit to hopefully make diagnosing the problem easier. In the query below I still have the same issue, I only get categories that are in the InvoiceDetail table.



SELECT
 c.CatID, 
 c.CategoryDescription as Category, 
IFNULL(SUM(CASE WHEN InvoiceDate between '2015-1-1' and '2015-12-31' THEN PubProfit ELSE 0 END),0) as LastYear
FROM  Categories c
Left Outer Join  `vCustomerInvoiceDetail` i on  c.CatID = i.ItemCategory
Where i.CustID = '10006'or i.CustID = NULL
Group by Category

Open in new window

0
 
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 41800361
Ah, ok, I see where I made my mistake.

Given the following category data in a table called #tmp_cat:
cat
And the following customer data in a table called #tmp_cust:
cust
You can use a CROSS JOIN to get all of the categories:
SELECT c.categoryid, SUM(CASE WHEN c.categoryid = i.catid THEN profit ELSE 0 END) AS totalprofit
FROM #tmp_cust i
CROSS JOIN #tmp_cat c
WHERE i.custid = 1
GROUP BY c.categoryid

Open in new window


Result:
result
0
 

Author Comment

by:dannyg280
ID: 41800429
Definitely getting closer. Using the query below I now get all of the categories, but my LastYear Column is now showing the grand total of all categories, and repeating it in every column.

So, when I run this:
SELECT c.CatID,
 c.CategoryDescription as Category,
 IFNULL(SUM(CASE WHEN InvoiceDate between '2015-1-1' and '2015-12-31' THEN PubProfit ELSE 0 END),0) as LastYear
 FROM Categories c
 Left Outer Join `vCustomerInvoiceDetail` i on c.CatID = i.ItemCategory
 Where i.CustID = '10006'or i.CustID = NULL Group by Category

Open in new window

I get: screenshot1
When I run this:
SELECT c.CatID,
 c.CategoryDescription as Category,
 IFNULL(SUM(CASE WHEN InvoiceDate between '2015-1-1' and '2015-12-31' THEN PubProfit ELSE 0 END),0) as LastYear
 FROM `vCustomerInvoiceDetail` i
 Cross Join `Categories` c 
Where i.CustID = '10006'or i.CustID = NULL 
Group by Category

Open in new window

I get this: Screenshot-2016-09-15-15.06.57.jpg
0
 
LVL 43

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 250 total points
ID: 41800509
You need to follow the logic in my example:

SUM(CASE WHEN c.categoryid = i.catid THEN profit ELSE 0 END) AS totalprofit

Open in new window


So, use a CASE statement to verify that you have the right category id before you include the amount in your total.

IFNULL(SUM(CASE WHEN InvoiceDate between '2015-1-1' and '2015-12-31' AND c.CatID = i.ItemCategory THEN PubProfit ELSE 0 END),0) as LastYear

Open in new window


so the full query looks like:

SELECT c.CatID,
 c.CategoryDescription as Category,
IFNULL(SUM(CASE WHEN InvoiceDate between '2015-1-1' and '2015-12-31' AND c.CatID = i.ItemCategory THEN PubProfit ELSE 0 END),0) as LastYear
 FROM `vCustomerInvoiceDetail` i
 Cross Join `Categories` c 
Where i.CustID = '10006'or i.CustID = NULL 
Group by Category

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 41800645
When summing be extremely cautious of doing that across joins. Joining tables multiplies the number of rows so it is VERY easy to get the wrong results in the sums. Most common solution is to aggregate in subqueries then join those resultsets.

MySQL has a peculiar ability that allows you to specify multiple columns in a select clause but only one of those columns in the group by clause. This is dangerous. To create robust queries do not use this technique.

{+edit}
This is a EXAMPLE of applying the 2 items mentioned above to your query
SELECT
        g.CustID
      , g.CustomerName
      , c.CatID
      , c.CategoryDescription AS Category
      , c.AvgTop150
      , g.CatGoal
      , g.CurrentRank
      , g.RankTimeStamp
      , ISNULL(g.lastyear, 0) AS LastYear
      , ISNULL(g.thisyear, 0) AS ThisYear
      , ISNULL(CASE
                WHEN g.lastyear > c.AvgTop150 THEN g.lastyear
                ELSE c.AvgTop150
        END, 0)               AS DefaultGoal
FROM Categories c
LEFT JOIN (SELECT
                cu.CustID
              , cu.CustomerName
              , i.ItemCategory
              , r.CatGoal
              , r.CurrentRank
              , r.RankTimeStamp
              , SUM(CASE
                        WHEN i.InvoiceDate >= '2015-01-01' AND
                                i.InvoiceDate < '2016-01-01' THEN PubProfit
                        ELSE 0
                END) AS LastYear
              , SUM(CASE
                        WHEN i.InvoiceDate >= '2016-01-01' AND
                                i.InvoiceDate < '2017-01-01' THEN PubProfit
                        ELSE 0
                END) AS ThisYear
        FROM vCustomerInvoiceDetail i
        INNER JOIN vCustomers cu ON i.CustID = cu.CustID
        LEFT OUTER JOIN GoalRankings r ON i.ItemCategory = r.CatID
                AND i.CustID = r.CustID
        WHERE i.CustID = @cid
        AND (i.InvoiceDate >= '2015-01-01'
        AND i.InvoiceDate < '2017-01-01')
        GROUP BY
                cu.CustID
              , cu.CustomerName
              , i.ItemCategory
              , r.CatGoal
              , r.CurrentRank
              , r.RankTimeStamp) AS g ON c.CatID = g.ItemCategory
WHERE c.CatID IN ('10', '20', '25', '30', '40', '75', '90')
;

Open in new window


That example query MIGHT produce more rows than you want because I cannot predict what this will do:
        GROUP BY
                cu.CustID
              , cu.CustomerName
              , i.ItemCategory
              , r.CatGoal
              , r.CurrentRank
              , r.RankTimeStamp

words like "current" and "timestamp" suggest to me that you have a many-to-one relationship and you relying on MySQL guesses the right one (yes MySQL can "guess")
you might need more logic for the table GoalRankings

===== beware of between ======
be very very very careful of using BETWEEN for date ranges, it is easy to get numeric errors when using that technique
a much more robust method is to use a combination of >= with < which you will see I have used above, but remember to use "the next day" (2017-01-01 instead of 2016-12-31)

for more on this topic please see: "Beware of Between"

note also. recent changes in MySQL make time information allows for sub-second precsion, so do NOT accept that  2016-12-31 23:59:59 is the end of that day, It isn't!
0
 

Author Comment

by:dannyg280
ID: 41801444
Thank you both! Both examples work and I've learned a great deal.
0
 
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 41801718
dannyg820 - if you're satisfied that your question has been answered, you can initiate the close process and identify the solution(s).
1

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

821 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