Tech Off Post

Single Post Permalink

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