Ask the Expert

Select from one table based on condition in another

I need to select all data from Table_A for employees in Unit ABC123, but the Unit code is in Table_B. The common key is Emplid. I need to create an Oracle Export script to basically replace the following code:

SELECT A.* FROM TABLE_A A
WHERE exists
(SELECT B.EMPLID from TABLE_B B
  where A.EMPLID = B.EMPLID 
    and B.UNIT   = 'ABC123')

Is an Export script possible?

    Requires Free Membership to View

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.

This was first published in January 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: