We have two tables, Districts and DayAllotment Districts is: DistrictID int Description varchar(25)
DayAllotment is: AllotmentID DistrictID DaysAllotted HalfYear Type Year
My Data in Day Allotment is something like this: 1 1012 23 First Fish 2008 2 1012 22 Second Fish 2008 3 1012 15 First Boat 2008 4 1012 14 Second Boat 2008 5 2012 23 First Boat 2008 6 2012 22 Second Fish 2008 7 2012 14 Second Boat 2008
So, let's say I want a result set that looks for All Fish allotments for 2008. I want a result set that looks like this: DistrictID First Second 1012 23 22 1013 NULL 22
I tried something like this:
SELECT dist.DistrictID, fDays.DaysAllotted AS 'Jan.-Jun.', sDays.DaysAllotted AS 'Jul.-Dec.' FROM dbo.law_Districts dist LEFT JOIN law_DeputyDaysAllotment fDays ON dist.DistrictID = fDays.DistrictID LEFT JOIN law_DeputyDaysAllotment sDays ON dist.DistrictID = sDays.DistrictID WHERE ((fDays.HalfYearWindow = 'First' AND fDays.Year = @Year AND fDays.Type = @Type) OR (fDays.HalfYearWindow IS NULL)) OR ((sDays.HalfYearWindow = 'Second' AND sDays.Year = @Year AND sDays.Type = @Type) OR (sDays.HalfYearWindow IS NULL))ORDER BY dist.DistrictID
But get the same results in both columns.
I've tried some other ways of joining, but have gotten results where some districts were dropped. I won't always know which side will have the NULL values.
I'm sure there are samples out there but I'm not sure what to search for. Would this be a cross join table?