All we need is an easy explanation of the problem, so here it is.
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|
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂