EXPERT RESPONSE
Sounds like what you're looking for is an inner join.
SELECT A.*
FROM TABLE_A A
INNER
JOIN TABLE_B B
ON B.EMPLID = A.EMPLID
AND B.UNIT = 'ABC123'
The only problem you will have is if the relationship
between TABLE_A and TABLE_B is one-to-many. Since you are
using the dreaded, evil "select star"—presumably you did this
only for convenience in posing the question, and would not do
it in real life—you will find it difficult to use GROUP BY,
because the GROUP BY would have to include every column being selected.
But there's an easier way. Push the GROUP BY down into a derived table
subquery, so that TABLE_A joins to only one row derived from TABLE_B:
SELECT A.*
FROM TABLE_A A
INNER
JOIN ( SELECT EMPLID
FROM TABLE_B
WHERE UNIT = 'ABC123'
GROUP
BY EMPLID ) B
ON B.EMPLID = A.EMPLID
This eliminates the one-to-many problem in the outer query.
|
