Tech Off Thread

2 posts

Should be simple SQL, but I'm missing something

Back to Forum: Tech Off
  • qwert231

    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?

  • dpratt71

    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 closed

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.