RE: SQL Join - all rows from any table for employee

Dennis,

I think you will need to use UNION to build a distinct list of
employees, then join that back to your three files (use left outer join
for each)... Not tested, and I seem to think this might not work.
Union might not be allowed in a CTE... In that case, you might need to
create the union as a QTEMP table.

With Emp (EmplNum) as
(select distinct EMPNO from DEDEMP
Union
Select distinct EMPNO from INCBAL
Union
Select distinct EMPNO from PRMAST
)

Select Emp.EmplNum, ...

From emp left outer join

DEDEMP on (emp.emplnum=dedemp.empno) left outer join
INCBAL on (emp.emplnum=incbal.empno) left outer join
PRMAST on ...

-Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
Sent: Thursday, June 10, 2010 2: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.