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

[CRPence <CRPbottle@xxxxxxxxx>]

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