Ryan Gadsdon Ryan Gadsdon - 2 years ago 159
SQL Question

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?

Answer Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download