Left Outer join Question

Question

Post reply

Left Outer join Question

jim Edwards-308192

Say Hey Kid

Points: 680
More actions
February 1, 2007 at 9:28 am

#176687

I want to return all the rows from my empper table even if they have no match on table empbplan. I thought my left outer join would accomplish this, but is my where statement screwing things up?
SELECT     empper.first_name, empper.last_name, empper.middle_name, empper.birth_date, empper.sex, empper.pers_status, empper.company,
                      empbplan.elig_code, empbplan.bplan_code
FROM         empper LEFT OUTER JOIN
                      empbplan ON empper.emp_id = empbplan.emp_id AND empper.company = empbplan.company
WHERE     (empper.pers_status = 'A') AND (empper.company = 'ABC') AND (empbplan.elig_code = 'A')
AND (empbplan.BPLAN_code = 'SAVINGS')

Thank You in advance

Viewing 5 posts - 1 through 4 (of 4 total)

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

kcdunn SSC Veteran Points: 281 More actions · Answer 1

Try

SELECT empper.first_name,

empper.last_name,

empper.middle_name,

empper.birth_date,

empper.sex,

empper.pers_status,

empper.company,

empbplan.elig_code,

empbplan.bplan_code

FROM empper

LEFT OUTER JOIN

empbplan

ON empper.emp_id = empbplan.emp_id

AND empper.company = empbplan.company

AND empbplan.elig_code = 'A'

AND empbplan.BPLAN_code = 'SAVINGS'

WHERE empper.pers_status = 'A'

AND empper.company = 'ABC'

jim Edwards-308192 Say Hey Kid Points: 680 More actions · Answer 2

Thanks! That worked. I'll need to read up on my joins to figure exactly what is going on here. It appears you have done "filtering" in the join statement on the empbplan table..

Have a good day!!

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

FYI, when you reference a column from the LEFT JOIN'ed table in the WHERE clause it creates an INNER JOIN.

This is another way to handle it ....

SELECT

empper.first_name

,empper.last_name

,empper.middle_name

,empper.birth_date

,empper.sex

,empper.pers_status

,empper.company

,empbplan.elig_code

,empbplan.bplan_code

FROM

empper

LEFT OUTER JOIN empbplan

ON empper.emp_id = empbplan.emp_id

AND empper.company = empbplan.company

WHERE

empper.pers_status = 'A'

AND empper.company = 'ABC'

AND (empbplan.elig_code = 'A' OR empbplan.elig_code IS NULL)

AND (empbplan.BPLAN_code = 'SAVINGS' OR empbplan.BPLAN_code IS NULL)

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

jim Edwards-308192 Say Hey Kid Points: 680 More actions · Answer 4

jim Edwards-308192

Say Hey Kid

Points: 680

February 2, 2007 at 1:51 pm

#687556

Thanks Jason!! I appreciate your advice also!