Dennis,
Do you need info from DEDBAL and INCBAL? If not, you could use EXISTS.
SELECT * <-- desired fields from PRMAST go here
FROM PRMAST
WHERE EXISTS (SELECT 1 FROM DEDBAL WHERE DEDEMP=PREMP)
OR EXISTS (SELECT 1 FROM INCBAL WHERE INCEMP=PREMP)
If you need to pull data from DEDBAL and INCBAL, you could use a LEFT OUTER
join
and then exclude those cases where an employee has no records in DEDEMP and
INCEMP.
Try this:
SELECT * <-- desired fields from PRMAST, DEDBAL, and INCBAL go here
FROM PRMAST
LEFT OUTER JOIN DEDBAL ON DEDEMP=PREMP
LEFT OUTER JOIN INCBAL ON INCEMP=PREMP
WHERE DEDEMP IS NOT NULL OR INCEMP IS NOT NULL
In this case, if an employee has records in DEDBAL but not in INCBAL, any
fields in INCBAL would be null and vice versa.
Have fun!
Richard Casey
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
Sent: Thursday, June 10, 2010 3:38 PM
To: 'Midrange Systems Technical Discussion'
Subject: SQL Join - all rows from any table for employee
We have three tables: DEDBAL, INCBAL, and PRMAST.
Each of these contains an employee number (DEDEMP, INCEMP, PREMP). We want
to produce output where an employee is represented in either DEDBAL and
INCBAL or both. (For the sake of argument, we can assume that PRMAST is
present for each employee).
I've tried various types of joins (LEFT OUTER, FULL, et cetera) and have
pored through the archives and GOOGLE and come up empty on a means of doing
this exactly right. (I'm convinced this is a common issue; so my issue is
probably my choice of search words - usually resulting in too many hits).
Would some kind soul mind directing me to guidelines on how to write the
JOIN for this challenge?
Thanks!
Dennis E. Lovelady
AIM/Skype: delovelady MSN: fastcounter@xxxxxxxxxxxx
<
http://www.linkedin.com/in/dennislovelady>
www.linkedin.com/in/dennislovelady --
I am at one with my duality.
midrange.com
