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

[James Perkins <jrperkinsjr@xxxxxxxxx>]

If I'm understanding correctly, a LEFT JOIN should work in conjunction with
a GROUP BY and HAVING clause.

SELECT a.emp_id
FROM prmast AS a
LEFT JOIN dedbal AS b ON a.emp_id = b.emp_id
LEFT JOIN incbal AS c ON a.emp_id = c.emp_id
GROUP BY a.emp_id
HAVING (COUNT(a.emp_id) > 1)

That should work, but it is untested.

--
James R. Perkins


On Thu, Jun 10, 2010 at 12:38, Dennis Lovelady <iseries@xxxxxxxxxxxx> 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 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.
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.