CODEWITHSUNDEEP
sql-server-2008-r2left-join
A.Steer
A.Steer

Reputation: 409

SQL server left join not returning expected records from left table

I have two objects within a SQl Server 2008 R2 database, which I am trying to join together with a left join but I am unable to get the left join to return all records from the table.

The vw_academicweeks, is a view that contains for each academic year a week number, and the first day and last day of the week and contains 52 records for each academic year.

tt_activityoccurrence is a table which contains occurrences of lessons within a year, lessons will not occur in all 52 weeks of the year.

With my query I am trying to return all instances from the vw_academicweeks view to return the following information

+------------+------------+------------+------------+---------+
| ActivityID | WeekStart  | StartTime  |  EndTime   | week_no |
+------------+------------+------------+------------+---------+
|      59936 | 04/09/2017 | 05/09/2017 | 05/09/2017 |       6 |
|      59936 | 11/09/2017 | 12/09/2017 | 12/09/2017 |       7 |
|      59936 | 18/09/2017 | 19/09/2017 | 19/09/2017 |       8 |
|      59936 | 25/09/2017 | 26/09/2017 | 26/09/2017 |       9 |
|      59936 | 02/10/2017 | 03/10/2017 | 03/10/2017 |      10 |
|      59936 | 09/10/2017 | 10/10/2017 | 10/10/2017 |      11 |
|      59936 | 16/10/2017 | 17/10/2017 | 17/10/2017 |      12 |
|      59936 | Null       | Null       | Null       |      13 |
|      59936 | 30/10/2017 | 31/10/2017 | 31/10/2017 |      14 |
|      59936 | 06/11/2017 | 07/11/2017 | 07/11/2017 |      15 |
|      59936 | 13/11/2017 | 14/11/2017 | 14/11/2017 |      16 |
|      59936 | 20/11/2017 | 21/11/2017 | 21/11/2017 |      17 |
|      59936 | 27/11/2017 | 28/11/2017 | 28/11/2017 |      18 |
|      59936 | 04/12/2017 | 05/12/2017 | 05/12/2017 |      19 |
|      59936 | 11/12/2017 | 12/12/2017 | 12/12/2017 |      20 |
|      59936 | 18/12/2017 | 19/12/2017 | 19/12/2017 |      21 |
|      59936 | Null       | Null       | Null       |      22 |
|      59936 | Null       | Null       | Null       |      23 |
+------------+------------+------------+------------+---------+

With the left join I can return all values except the nulls, so that the week_no column is missing rows, 13,22 and 23. I have also tried this with an outer join but receive the same information.

I feel I am missing something obvious but it is escaping me at the moment. 

select 
    ttao.ActivityID
    ,dateadd(dd,datediff(dd,0,DATEADD(dd, -(DATEPART(dw, ttao.StartTime)-1), ttao.StartTime)),0) WeekStart
    ,ttao.StartTime
    ,ttao.EndTime
    ,aw.week_no

from

vw_AcademicWeeks AW
left join TT_ActivityOccurrence TTAO on
(dateadd(dd,datediff(dd,0,DATEADD(dd, -(DATEPART(dw, ttao.StartTime)-1), ttao.StartTime)),0))=aw.ay_start
where 

ay_code='1718' and
TTAO.ActivityID='59936'

order by aw.week_no asc

Upvotes: 0

Views: 68

Answers (1)

S3S
S3S

Reputation: 25152

Your where clause makes it an inner join by eliminating rows outside of the scope of your join. You need to move this logic up to your join statement. Note, I didn't validate your join condiditon (the dateadd...datediff logic)

select 
    ttao.ActivityID
    ,dateadd(dd,datediff(dd,0,DATEADD(dd, -(DATEPART(dw, ttao.StartTime)-1), ttao.StartTime)),0) WeekStart
    ,ttao.StartTime
    ,ttao.EndTime
    ,aw.week_no    
from    
vw_AcademicWeeks AW
left join TT_ActivityOccurrence TTAO on
    (dateadd(dd,datediff(dd,0,DATEADD(dd, -(DATEPART(dw, ttao.StartTime)-1), ttao.StartTime)),0)) = aw.ay_start
    and ay_code='1718' 
    and TTAO.ActivityID='59936'    
order by aw.week_no asc

Upvotes: 1

Related Questions