Tech Off Post

Single Post Permalink

View Thread: Should be simple SQL, but I'm missing something
  • User profile image
    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?