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`…

Accepted Answer

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.

Left join with condition on the right table

Solution

Problem

Related problems