All geek questions in one place

SQL query LEFT JOIN

Ok ... I'm always scared with LEFT JOINS for some reason in SQL.

I have a simple request

SELECT COUNT(*) as OpenedToday, c.Product_Line, c.Product_Code FROM SFCase as c LEFT OUTER JOIN (SELECT DISTINCT Product_Code from SFCase) as p ON p.Product_Code = c.Product_Code WHERE IsClosed = 'false' AND DATEPART(YEAR, GETDATE()) = DATEPART(YEAR, CreatedDate) AND DATEPART(MONTH, GETDATE()) = DATEPART(MONTH, CreatedDate) AND DATEPART(DAY, GETDATE()) = DATEPART(DAY, CreatedDate) GROUP BY c.Product_Line, c.Product_Code

What I expect is a list of all Product_Code, with the number of OpenedToday cases (including null values). Instead, I get only a list of those product codes that are open today (only positive values).

When I run only DISTINCT Product_Code, I get 70 results. However, by doing a full request, I get only 4 today. I would like to see all 70 results, as well as zeros if there were no open cases today.

What am I doing wrong with this connection?

Charlie

+4
source share
2 answers

Try:

 SELECT SUM(CASE WHEN IsClosed='false' AND DATEDIFF(DAY, GETDATE(), CreatedDate)=0 THEN 1 ELSE 0 END) as OpenedToday, Product_Line, Product_Code FROM SFCase GROUP BY Product_Line, Product_Code
+2
source 
 SELECT p.Product_Line, p.Product_Code,COUNT(*) as OpenedToday FROM SFCase as c RIGHT OUTER JOIN (SELECT DISTINCT Product_Code from SFCase) as p ON p.Product_Code = c.Product_Code GROUP BY p.Product_Line, p.Product_Code
+1
source

Source: https://habr.com/ru/post/1490317/

More articles:


All Articles