Left Outer Join Calculation

Question

Left Outer Join Calculation

SparTodd

Right there with Babe

Points: 717
More actions
September 29, 2009 at 10:31 am

#135956

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.

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

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 Right there with Babe Points: 717 More actions · Answer 2

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: 184394 More actions · Answer 3

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 Right there with Babe Points: 717 More actions · Answer 4

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: 9376 More actions · Answer 5

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 Right there with Babe Points: 717 More actions · Answer 6

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.