CODEWITHSUNDEEP
sqlsql-serverjoin
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Left join not producing correct result

I want to return all people regardless of whether they have a sale on any given day. My initial response to solve this would be

SELECT 
    P.[Name], P.[id], 
    F.[DATE], F.[Figure], F.[id]
FROM
    Person P
LEFT JOIN 
    Figure F ON P.id = F.id

An example of the result for this is

 Name           id      DATE         Figure
--------------------------------------------
 Tom            1       2017-09-27   15
 Fred           2       2017-09-27   20
 Jane           3       2017-09-25   0
 Ben            4       2017-09-25   0

Now as soon as I limit the date to today's date, I get nothing.

SELECT 
    P.[Name], P.[id], 
    F.[DATE], F.[Figure], F.[id]
FROM
    Person P
LEFT JOIN 
    Figure F ON P.id = F.id
WHERE 
    F.Date = GETDATE()

I understand this is probably because there is no one with today's date attached to them but how do I still display all of the people even if they had nothing for today? I thought that a left join would accomplish this but it doesn't.

My desired results would be :

 Name           id      DATE         Figure
--------------------------------------------
 Tom            1       2017-10-02   0
 Fred           2       2017-10-02   0
 Jane           3       2017-10-02   0
 Ben            4       2017-10-02   0

There are no keys in the tables so I feel as though that might be why the left join isn't working as I expected but has anyone got any ideas how I can get the desired result?

Upvotes: 1

Views: 67

Answers (2)

Simon
Simon

Reputation: 1211

Put your query in a cte and then pull from it to achieve your desired results:

;with cte_example
as
(SELECT 
P.[Name], 
P.[id], 
F.[DATE], F.[Figure], F.[id]
FROM
Person P
LEFT JOIN Figure F
ON P.id = F.id)

select Name
       ,id
       ,cast(getdate() as date) as date
       ,case when date = convert(date, getdate()) then Figure else 0 end as Figure
from cte_example

I made a rextester example so you could see

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

First, the condition needs to go in the ON clause rather than the WHERE clause, because it is on the second table.

Second, getdate() -- despite its name -- has a time component. So, convert it to a date:

FROM Person P LEFT JOIN
     Figure F
     ON P.id = F.id AND F.Date = CAST(getdate() as DATE)

Upvotes: 2

Related Questions