Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    17

    Unanswered: Left Joins where tables are query sources

    I'm building an incredibly irritating omnibus report for one of my managers and I'll confess I'm stuck.

    I've managed to cram 5 queries into one, but I can't for the life of me figure out how to join them, right now I'm using an = operator in the where statement to simulate an inner join, but what I really need is a Left Outer Join.

    Attached is the query, sorry for the ugliness, I haven't cleaned it up and formatted it yet.

    Thanks for all your help once more.

    Rob
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm going to help you with this, but only because I was incredibly evil in a past life and I'm desperately trying to atone:


    Select lower(SD.assoc_name) as 'AssocName',
    lower(SD.mngr_name) as 'MngrName',
    lower(SD.wg_name) as 'WGName',
    MoDataReturn,
    MoDataSent,
    MoDataRate,
    MoDataScore,
    sum(SD.return_status) as 'YTDReturn',
    count(*) as 'YTDSent',
    sum(SD.return_status)/cast(count(*) as numeric) as 'YTDRate',
    YTDScore
    from tblSurveyData SD,
    left outer join
    (SELECT avg(SurvScore) as 'YTDScore',
    SurvAssoc as 'YTDAssocID'
    FROM (SELECT SUM(RE.Response)/COUNT(RE.Response) AS 'SurvScore',
    SD.objid as 'SurvID',
    SD.Assoc_id as 'SurvAssoc'
    FROM tblSurveyData SD
    INNER JOIN tblResponses RE ON RE.Return_Key = SD.objid
    GROUP BY SD.objid,SD.assoc_id) alTable
    GROUP BY SurvAssoc) alYTD
    on SD.assoc_id=alYTD.YTDAssocID
    left outer join
    (Select MoAssocID as 'MoDataAssocID',
    sum(return_status) as 'MoDataReturn',
    count(*) as 'MoDataSent',
    sum(return_status)/cast(count(*) as numeric) as 'MoDataRate',
    MoSurvScore as 'MoDataScore'
    from tblSurveyData SD,
    (SELECT avg(SurvScore) as 'MoSurvScore',
    SurvAssoc as 'MoAssocID'
    FROM (SELECT SUM(RE.Response)/COUNT(RE.Response) AS 'SurvScore',
    SD.objid as 'SurvID',
    SD.Assoc_id as 'SurvAssoc'
    FROM tblSurveyData SD
    INNER JOIN tblResponses RE ON RE.Return_Key = SD.objid
    WHERE SD.return_date>='2003-09-29'
    and SD.return_date >='10-26-2003'
    GROUP BY SD.objid,SD.assoc_id) MoRawScore
    GROUP BY SurvAssoc) MoSurvScore
    on SD.assoc_id = MoAssocID
    and SD.region_id=52
    group by MoAssocID,
    MoSurvScore) alMOS
    on SD.assoc_id=alMOS.MoDataAssocID
    where SD.region_id=52
    group by SD.assoc_name,
    YTDScore,
    SD.mngr_name,
    SD.wg_name,
    MoDataReturn,
    MoDataSent,
    MoDataRate,
    MoDataScore
    order by SD.mngr_name,
    SD.assoc_name,
    SD.wg_name

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A few syntax errors fixed:

    Select lower(SD.assoc_name) as 'AssocName',
    lower(SD.mngr_name) as 'MngrName',
    lower(SD.wg_name) as 'WGName',
    MoDataReturn,
    MoDataSent,
    MoDataRate,
    MoDataScore,
    sum(SD.return_status) as 'YTDReturn',
    count(*) as 'YTDSent',
    sum(SD.return_status)/cast(count(*) as numeric) as 'YTDRate',
    YTDScore
    from tblSurveyData SD
    left outer join
    (SELECT avg(SurvScore) as 'YTDScore',
    SurvAssoc as 'YTDAssocID'
    FROM (SELECT SUM(RE.Response)/COUNT(RE.Response) AS 'SurvScore',
    SD.objid as 'SurvID',
    SD.Assoc_id as 'SurvAssoc'
    FROM tblSurveyData SD
    INNER JOIN tblResponses RE ON RE.Return_Key = SD.objid
    GROUP BY SD.objid,SD.assoc_id) alTable
    GROUP BY SurvAssoc) alYTD
    on SD.assoc_id=alYTD.YTDAssocID
    left outer join
    (Select MoAssocID as 'MoDataAssocID',
    sum(return_status) as 'MoDataReturn',
    count(*) as 'MoDataSent',
    sum(return_status)/cast(count(*) as numeric) as 'MoDataRate',
    MoSurvScore as 'MoDataScore'
    from tblSurveyData SD,
    (SELECT avg(SurvScore) as 'MoSurvScore',
    SurvAssoc as 'MoAssocID'
    FROM (SELECT SUM(RE.Response)/COUNT(RE.Response) AS 'SurvScore',
    SD.objid as 'SurvID',
    SD.Assoc_id as 'SurvAssoc'
    FROM tblSurveyData SD
    INNER JOIN tblResponses RE ON RE.Return_Key = SD.objid
    WHERE SD.return_date>='2003-09-29'
    and SD.return_date >='10-26-2003'
    GROUP BY SD.objid,
    SD.assoc_id) MoRawScore
    GROUP BY SurvAssoc) MoSurvScore
    group by MoAssocID,
    MoSurvScore) alMOS
    on SD.assoc_id = MoDataAssocID
    and SD.region_id=52
    where SD.region_id=52
    group by SD.assoc_name,
    YTDScore,
    SD.mngr_name,
    SD.wg_name,
    MoDataReturn,
    MoDataSent,
    MoDataRate,
    MoDataScore
    order by SD.mngr_name,
    SD.assoc_name,
    SD.wg_name

  4. #4
    Join Date
    Nov 2003
    Posts
    17

    Damn I'm dumb

    Thanks much for your help, after reading your post I realized that I was using "Left Join" instead of "Left Outer Join"

    Damn I'm dumb, and your a good person for reading through that evil query, and responding.

    Greatest of thanks from a humble web developer.

    Rob

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •