Left join with condition on the right table

Question

I'm trying to use LEFT JOIN with a condition on the right table and I'm getting a lot problems doing so.

I have two tables:

projects{project_id,start_date}
projectForeCast{project_id,year_number,month_number,hours}

I'm trying to get all projects that opened in the last week and the hours that wererecorded in the last month.

SELECT      dbo.Project.PROJECT_ID, dbo.ProjectForeCast.HOURS AS F0
FROM         dbo.Project LEFT JOIN  dbo.ProjectForeCast ON dbo.Project.PROJECT_ID = dbo.ProjectForeCast.PROJECT_ID
WHERE   (dbo.ProjectForeCast.YEAR_NUMBER = DATEPART(YYYY, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE())))) AND 
                      (dbo.ProjectForeCast.MONTH_NUMBER = DATEPART(MM, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE())))) AND 
                      (DATEPART(WK,dbo.Project.START_DATE) = DATEPART(WK, DATEADD(WK, - 1, GETDATE())))AND
                      (DATEPART(YYYY,dbo.Project.START_DATE) = DATEPART(YYYY, DATEADD(WK, - 1, GETDATE())))

It's working just fine but if the project don't have a record in projectForeCast in the last month_number I don't get the project at all. I want to get an empty cell or null in the column F0 in this case. This is the reason I tried the LEFT JOIN but it didn't work.

Outer join is what you want. Left or Right says which table it's allowed to be null in. — Tony Hopkinson, May 06 '12 at 12:40

score 4 · Accepted Answer · answered May 06 '12 at 19:44

As my previous experience, I would write your SQL query as like:

SELECT p.PROJECT_ID, pfc.HOURS AS F0
FROM 
(   SELECT dbo.Project.PROJECT_ID FROM dbo.Project q 
    WHERE (DATEPART(WK,dbo.Project.START_DATE) = DATEPART(WK, DATEADD(WK, - 1, GETDATE())))AND
    (DATEPART(YYYY,dbo.Project.START_DATE) = DATEPART(YYYY, DATEADD(WK, - 1, GETDATE())))
) p
LEFT JOIN  
(   SELECT dbo.ProjectForeCast.HOURS FROM dbo.ProjectForeCast 
    WHERE (dbo.ProjectForeCast.YEAR_NUMBER = DATEPART(YYYY, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE())))) AND 
    (dbo.ProjectForeCast.MONTH_NUMBER = DATEPART(MM, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE()))))
) pfc
ON p.PROJECT_ID = pfc.PROJECT_ID

OR using table aliases that SQL statement will be more readable:

SELECT p.PROJECT_ID, pfc.HOURS AS F0
FROM 
(   SELECT pr.PROJECT_ID FROM dbo.Project pr 
    WHERE (DATEPART(WK,pr.START_DATE) = DATEPART(WK, DATEADD(WK, - 1, GETDATE())))AND
    (DATEPART(YYYY,pr.START_DATE) = DATEPART(YYYY, DATEADD(WK, - 1, GETDATE())))
) p
LEFT JOIN  
(   SELECT pf.HOURS FROM dbo.ProjectForeCast pf
    WHERE (pf.YEAR_NUMBER = DATEPART(YYYY, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE())))) AND 
    (pf.MONTH_NUMBER = DATEPART(MM, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE()))))
) pfc
ON p.PROJECT_ID = pfc.PROJECT_ID

I think you will get correct result with above that query.

John Dewey · Answer 2 · 2012-05-06T16:21:34.613

Try moving your WHERE clauses into your LEFT JOIN.

DECLARE @YEAR_NUMBER1 INT; SET @YEAR_NUMBER1=DATEPART(YYYY, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE())));
DECLARE @MONTH_NUMBER1 INT; SET @MONTH_NUMBER1=DATEPART(MM, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE())));
DECLARE @YEAR_NUMBER2 INT; SET @YEAR_NUMBER2=DATEPART(YYYY, DATEADD(WK, - 1, GETDATE()));
DECLARE @WEEK_NUMBER1 INT; SET @WEEK_NUMBER1=DATEPART(WK, DATEADD(WK, - 1, GETDATE()));

SELECT p.PROJECT_ID
, pfc.HOURS AS F0
FROM  project p
LEFT JOIN  projectForeCast pfc 
    ON p.PROJECT_ID = pfc.PROJECT_ID
    AND pfc.YEAR_NUMBER = @YEAR_NUMBER1
    AND pfc.MONTH_NUMBER = @MONTH_NUMBER1
    AND DATEPART(YYYY,p.[START_DATE]) = @YEAR_NUMBER2
    AND DATEPART(WK,p.[START_DATE]) = @WEEK_NUMBER1;
GO

A couple of additional quick tips:

table aliases will make your code more readable
function calls in WHERE and JOIN will slow your query down, so replace them with variables whenever possible.

score 0 · Answer 3 · answered May 06 '12 at 12:41

0

You need to check for NULl where you refer to project forecast in the where statement.

The join is working, but the second table is getting null values. Either use COALESCE or IS NULL.

answered May 06 '12 at 12:41

Gordon Linoff

1,242,037
58
646
786

OP claims that the project isn't being returned which indicates an issue in the where clause because the proejcts table is the left table. – Chris Gessler May 06 '12 at 13:19
They are not being returned because of NULLs in the values of the second table. – Gordon Linoff May 06 '12 at 18:57

score 0 · Answer 4 · answered May 06 '12 at 12:55

that's expected. If you do a left join, you will get all records from the left table even though you dont have records on the right table but since you are saying on the where clause that you want records on the right table WHERE YEAR_NUMBER (for example) satisfies a condition, you are pretty much making it a regular join because, if the records dont exist, they are null and null compared with anything results null.

what you have to do is something like YEAR_NUMBER = the_year_you_want OR YEAR_NUMBER is null to deal with the cases when you dont have projectForeCast

also remember to use () on each condition

score 0 · Answer 5 · edited May 23 '17 at 11:45

When you do a LEFT JOIN or RIGHT JOIN, it makes a difference whether you put the condition in the JOIN clause or in the WHERE clause.

Check this answer for an in-depth explanation with examples:
What is the difference in these two queries as getting two different result set?

--> If you want to get the projects that don't have a row in projectForeCast as well, then you have to put your whole condition in the JOIN clause instead of the WHERE clause.

Chris Gessler · Answer 6 · 2012-05-06T13:39:02.933

LEFT JOIN will return all records from the project table so your issue is with your WHERE clause and I beleive the problem is that you're using DATEPART 'WEEK' for last week. Instead you should be using 'ISOWEEK' or calculating it yourself pre SQL2008.

DECLARE @TodayDayOfWeek INT
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime

--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @TodayDayOfWeek = datepart(dw, GetDate())
--get the last day of the previous week (last Sunday)
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())
--get the first day of the previous week (the Monday before last)
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())

To test this theory, just remove the LEFT JOIN leaving only the project table and the related where clause. You should see the same project list.

Another exercise would be to select the individual dateparts to make sure they match properly. I think you'll find that the DATEPART(WK...) isn't giving you the expected result.

Another thing to note is that you're subtracting 1 week which will give you different results when ran on Monday vs Tuesday. When you say "last week", so you mean literally Today - 7 or do you mean last week as in Sun-Sat?

Try this query to see if it fixes your issue.

DECLARE @Today DATETIME,
        @TodayDayOfWeek INT, 
        @EndOfPrevWeek DATETIME, 
        @StartOfPrevWeek DATETIME, 
        @MonthPart1 INT,
        @MonthPart2 INT,
        @YearPart1 INT,
        @YearPart2 INT 

-- get a date range consisting of 'last week'
SET @Today = GETDATE()
--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday) 
SET @TodayDayOfWeek = datepart(dw, @Today) 
--get the last day of the previous week (last Sunday) 
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, @Today) 
--get the first day of the previous week (the Monday before last) 
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), @Today) 

--last week could span months or even years (i.e. Dec/Jan)
SET @MonthPart1 = DATEPART(MM, @StartOfPrevWeek)
SET @MonthPart2 = DATEPART(MM, @EndOfPrevWeek)

SET @YearPart1 = DATEPART(YYYY, @StartOfPrevWeek)
SET @YearPart2 = DATEPART(YYYY, @EndOfPrevWeek)

SELECT      
            dbo.Project.PROJECT_ID, 
            dbo.ProjectForeCast.HOURS AS F0
FROM         
            dbo.Project 
LEFT JOIN   dbo.ProjectForeCast ON dbo.Project.PROJECT_ID = dbo.ProjectForeCast.PROJECT_ID
WHERE       dbo.ProjectForeCast.YEAR_NUMBER IN (@YearPart1, @YearPart2) 
AND         dbo.ProjectForeCast.MONTH_NUMBER IN (@MonthPart1, @MonthPart2) 
AND         dbo.Project.START_DATE BETWEEN @StartOfPrevWeek AND @EndOfPrevWeek

Left join with condition on the right table

6 Answers6