Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 92
  • Last Modified:

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

0
dannyg280
Asked:
dannyg280
  • 4
  • 4
  • 2
  • +1
2 Solutions
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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)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
 
PortletPaulCommented:
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
 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now