Sql left outer join issue

Question

0.00/5 (No votes)

See more:

Hi! I have at least 15 years experience with SQL but I'm drowning on this one :(
Would like to always retrieve 24 rows (from hour 0 to 23) even if there are no sales, hence the left join. I'd expect an hour with no sales to show NULL on column Sales.
So trying to retrieve Sales by Hour.

Left table:
Dim_Hours
Id - int
0
1
2
3
...
23

Right table:
Sales
DateTimeSale - datetime
Sale - int
1/3/2022 9:05, 100
1/4/2022 10:45, 200

SQL

SELECT          Dim_Hours.Id, SUM(Sales.Sale) AS Sales
FROM            Dim_Hours
LEFT OUTER JOIN Sales ON Dim_Hours.Id = DATEPART(hour, Sales.DateTimeSale)
WHERE           Sales.DateTimeSale BETWEEN '2022-1-1' AND '2022-1-31'
GROUP BY        Dim_Hours.Id
ORDER BY        Id

but I'm only getting the hours where there were sales therefore there must be something wrong with my LEFT OUTER JOIN above. Query was written using "Design Query in Editor" in SSMS. I explicitly marked "Select All Rows from Dim_Hours". Tables above were simplified for illustration purpose.
Could you please provide a hint on where I'm missing the whole point of this Left Join?
Thanks!
ST

What I have tried:

Tried query above, read lots of good articles on JOINS but still stuck. I must have written thousands of joins but this one is killing me.

Posted 6-Feb-22 13:11pm

sterenas

Updated 7-Feb-22 0:41am

Add a Solution

2 solutions

Solution 1

IMHO it is the WHERE clause that causes you to get only hours with sales.

I'd try something like

WHERE Sales.DateTimeSale IS NULL OR Sales.DateTimeSale BETWEEN ...

:)

Posted 6-Feb-22 18:19pm

Luc Pattyn

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2022-02-07T00:41:00

As Luc said, the filter in the WHERE clause turns your LEFT JOIN into an INNER JOIN.

The simplest solution is to put the filter in the join conditions rather than the WHERE clause:

SQL

SELECT          Dim_Hours.Id, SUM(Sales.Sale) AS Sales
FROM            Dim_Hours
LEFT OUTER JOIN Sales 
                ON Dim_Hours.Id = DATEPART(hour, Sales.DateTimeSale)
                AND Sales.DateTimeSale BETWEEN '2022-1-1' AND '2022-1-31'
GROUP BY        Dim_Hours.Id
ORDER BY        Id