# Left Outer Join Calculation

• 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.

• 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.

• 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.

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

• 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.

• 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