Solved

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

Posted on 2016-09-15
11
37 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 49

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 49

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
 
LVL 42

Expert Comment

by:zephyr_hex
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 42

Expert Comment

by:zephyr_hex
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 42

Assisted Solution

by:zephyr_hex
zephyr_hex 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 42

Expert Comment

by:zephyr_hex
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now