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

On 10-Jun-2010 14:38, Dennis Lovelady wrote:

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 <ed> each employee is
present in PRMAST).

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?

<code>

create table prmast (premp int not null, primary key (premp))
;
create table dedbal (dedemp int, foreign key (dedemp) references prmast (premp))
;
create table incbal (incemp int, foreign key (incemp) references prmast (premp))
;
insert into prmast values (1),(2),(3),(4),(5),(6),(7),(8),(9)
;
insert into dedbal values (3),(6),(9)
;
insert into incbal values (2),(4),(6),(8)
;
/* The following two requests produce the later\same report */
select p.premp
from prmast p
where exists (select '1' from dedbal d where p.premp=d.dedemp)
or exists (select '1' from incbal i where p.premp=i.incemp)
order by p.premp
;
select d.dedemp as premp
from dedbal d
union /* distinct */
select i.incemp as premp
from incbal i
order by premp
;
/* This report shows each PREMP value that is in either of */
/* or in both of the files DEDBAL and INCBAL */
....+....1....
PREMP
2
3
4
6
8
9
**** End of data ****

</code>

Regards, Chuck