Left Outer Join Calculation

  • SparTodd

    SSChasing Mays

    Points: 600

    I know, you're probably rolling your eyes at this question, but despite Googling this to death I'm still stumped so please bear with me.

    My issue is that I'm trying to calculate a sum for matching records from the right table which meet certain conditions. If there are no matching records on the right, or the matching records on the right don't meet the specified conditions, I still want the records from the left table displayed with a Null value in the calculated column.

    Breaking down the two selections, the select from the left table would be as follows, yielding 1,212 records:

    SELECT MSTBALP.CLIENT, MSTBALP.MACTNM, MSTBALP.CLTNUM

    FROM MSTBALP

    WHERE MSTBALP.CLIENT = 'BME'

    ORDER BY MSTBALP.CLIENT, MSTBALP.CLTNUM

    The selection on the right table would give me 227 records:

    SELECT BILPRCP.CLIENT, BILPRCP.MACTNM, SUM(BILPRCP.PBFEE) AS INSBAL

    FROM BILPRCP

    WHERE BILPRCP.CLIENT = 'BME' AND BILPRCP.PBINS <> 5 AND BILPRCP.CHKDAY = 0 AND BILPRCP.ADJAMT = 0

    GROUP BY BILPRCP.CLIENT, BILPRCP.MACTNM

    ORDER BY BILPRCP.CLIENT, BILPRCP.MACTNM

    Yet trying to invoke the left out join to achieve the results I want still gives me only the 227 records because of the where criteria on the right table.

    SELECT MSTBALP.CLIENT, MSTBALP.MACTNM, MSTBALP.CLTNUM, SUM(BILPRCP.PBFEE) AS INSBAL

    FROM MSTBALP

    LEFT OUTER JOIN BILPRCP

    ON MSTBALP.CLIENT = BILPRCP.CLIENT AND MSTBALP.MACTNM = BILPRCP.MACTNM

    WHERE MSTBALP.CLIENT = 'BME' AND BILPRCP.PBINS <> 5 AND BILPRCP.CHKDAY = 0 AND BILPRCP.ADJAMT = 0

    GROUP BY MSTBALP.CLIENT, MSTBALP.CLTNUM, MSTBALP.MACTNM

    ORDER BY MSTBALP.CLIENT, MSTBALP.CLTNUM

    Any help on pounding the correct method for this into my thick skull would be greatly appreciated.

  • Jack Corbett

    SSC Guru

    Points: 184371

    I believe moving the filters on BILPRCP from the WHERE clause to the ON clause will return the results you are expecting. Like this:

    SELECT

    MSTBALP.CLIENT,

    MSTBALP.MACTNM,

    MSTBALP.CLTNUM,

    SUM(BILPRCP.PBFEE) AS INSBAL

    FROM

    MSTBALP LEFT OUTER JOIN

    BILPRCP

    ON MSTBALP.CLIENT = BILPRCP.CLIENT AND

    MSTBALP.MACTNM = BILPRCP.MACTNM AND

    BILPRCP.PBINS <> 5 AND

    BILPRCP.CHKDAY = 0 AND

    BILPRCP.ADJAMT = 0

    WHERE

    MSTBALP.CLIENT = 'BME'

    GROUP BY

    MSTBALP.CLIENT,

    MSTBALP.CLTNUM,

    MSTBALP.MACTNM

    ORDER BY

    MSTBALP.CLIENT,

    MSTBALP.CLTNUM

    If that doesn't work convert your query of BILPRCP to a CTE or derived table and LEFT JOIN to it.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • SparTodd

    SSChasing Mays

    Points: 600

    Thanks, Jack. That worked beautifully. I get it now. I had seen examples of moving the select criteria to the "On" statement but is amazing how much it is easier to understand when you see it applied to your own situation. You've made my day.

  • Jack Corbett

    SSC Guru

    Points: 184371

    No problem. Glad I could help. It really makes sense if you look into how SQL Server processes queries. Search for Logical Query Processing.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • SparTodd

    SSChasing Mays

    Points: 600

    Thanks for the advice; I'll look into that. As you can probably tell, I'm pretty new at this stuff, learning as I go. I'm sure that studying the area suggested will help me out a lot.

  • Piotr.Rodak

    SSCrazy Eights

    Points: 9296

    It may be probably worth to mention that having the right table conditions in the WHERE clause made the left join effectively an INNER join, because the conditions filtered out the null values for nonmatched rows.

    Regards

    Piotr

    ...and your only reply is slàinte mhath

  • SparTodd

    SSChasing Mays

    Points: 600

    Thanks, piotr. Once Jack showed me the errors of my ways it made perfect sense how my results were as they were, exactly as you outlined. That was an important lesson for me today.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply