How to do a specific type of join for two tables in SQL Server?

Question

Fiddle: http://sqlfiddle.com/#!18/76ced/2

I've got two tables - Roster and Sample. The Roster table contains information on what days an employee has worked across a period of two weeks. The Sample table contains a sample weekly roster.

I want to know how the days in Roster and Sample differ across each week of the Roster. Basically want something like the below table:

Week	Roster Day	Sample Day
1	NULL	Monday
1	Tuesday	Tuesday
1	Wednesday	Wednesday
1	Thursday	Thursday
2	Monday	Monday
2	Tuesday	Tuesday
2	Wednesday	Wednesday
2	Thursday	Thursday
2	Friday	NULL

I've tried doing a simple outer join between the two tables but that did not give the desired result. It looks like I may need to do a cross join but I'm not sure how to incorporate that either. Any help would be appreciated.

Answer 1

You want to return rows from either side of the join when there is no matching row from the other row source, this requires a full outer join rather than just a standard left outer join.

Just doing the full join on it's own is not enough, as you still want to return a null for 'Monday' on week 1 even though there is a 'Monday' that would make the join. For this requirement you need to make sure both these columns are somehow involved in the full join. For this, you can cross join a distinct set of weeks and days to get a starting point.

http://sqlfiddle.com/#!18/76ced/32

select coalesce(r.week, w.week) week, r.day roster_day, d.sample_day
from (select distinct week from roster) w
cross join (select [Day] sample_day from sample) d
full join roster r
  on  r.week = w.week
 and  r.day  = d.sample_day
order by 1

Note that the ordering is going to be pseudorandom without you adding something (so that 'Monday' is considered less than 'Friday'). You could do this be ordering by a case when expression against the day columns (something like case when r.day = 'Monday' then 1 when r.day = 'Tuesday' then 2... etc). It is a happy coincidence (although not completely unexpected given the access paths available and small data sets) that the SQL Fiddle returns results in the order you might want.