Tech Off Thread

2 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Should be simple SQL, but I'm missing something

Back to Forum: Tech Off
  • User profile image

    We have two tables, Districts and DayAllotment
    Districts is:
    DistrictID int
    Description varchar(25)

    DayAllotment is:

    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?

  • User profile image

    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):

        COALESCE(fDays.DistrictID, sDays.DistrictID) DistrictID
        , fDays.DaysAllotted AS 'Jan.-Jun.'
        , sDays.DaysAllotted AS 'Jul.-Dec.'
        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.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.