Solved

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

Posted on 2016-09-15
11
71 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
[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
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 52

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 52

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 49

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

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…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

707 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