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?
Definitely not a cross join. You want a "full outer join" (includes rows from both tables even when they don't match). Here's the basic idea (minus the "where" clause):
SELECT COALESCE(fDays.DistrictID, sDays.DistrictID) DistrictID , fDays.DaysAllotted AS 'Jan.-Jun.' , sDays.DaysAllotted AS 'Jul.-Dec.' FROM law_DeputyDaysAllotment fDays FULL OUTER JOIN law_DeputyDaysAllotment sDays ON fDays.DistrictID = sDays.DistrictID AND fDays.Year = sDays.Year AND fDays.Type = sDays.Type
I did this by hand and didn't check it, so there may be one or two syntax errors lurking in there.
Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.