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

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

dannyg280Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PaulConnect With a Mentor Commented:
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
 
Ryan ChongCommented:
>>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
 
Ryan ChongCommented:
like:

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

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dannyg280Author Commented:
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
 
zephyr_hex (Megan)DeveloperCommented:
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
 
dannyg280Author Commented:
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
 
zephyr_hex (Megan)DeveloperCommented:
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
 
dannyg280Author Commented:
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
 
zephyr_hex (Megan)Connect With a Mentor DeveloperCommented:
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
 
dannyg280Author Commented:
Thank you both! Both examples work and I've learned a great deal.
0
 
zephyr_hex (Megan)DeveloperCommented:
dannyg820 - if you're satisfied that your question has been answered, you can initiate the close process and identify the solution(s).
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.