Jim --

Thanks for your example, Jim.

I guess I'd say that this example falls into the 1st category I mentioned -- that of a self-join when records have a relationship to one another. In this case, because there is a record for each training program, one grasps a student's status only by reviewing multiple records in the training table.

But, how about this as alternate syntax? It still uses the subquery.

SELECT Employee.* ;
  FROM Employee ;
  WHERE Employee.EmpID IN ;
     (SELECT Employee.EmpID ;
         FROM Employee JOIN Training ;
            ON Employee.EmpId = Training.EmpId ;
            AND Training.Program IN ("Orientation", "OSHA") ;
            GROUP BY Employee.EmpID ;
            HAVING COUNT (Employee.EmpID) = 2)

But, the subquery is formulated so that a subquery is not needed for each condition.

Jay



>Jay,
>
>Here's a fairly good example for using subselects.
>
>Background:
>
>Employee table one rec of each person
>
>Training table one rec for each training program for each person
>
>employees are "on probation" until they complete their orientation and OSHA training programs
>
>Question:
>create a list of employees that are NOT on probation
>
>Answer:
>There is only one way to get the answer and that is;
>
>

>SELECT Employee.* ;
>  FROM Employee JOIN Training ;
>    ON Employee.EmpId = Training.EmpId ;
> WHERE Training.Program = "Orientation" ;
>   AND Employee.EmpId IN ;
>       (SELECT EmpId ;
>          FROM Training ;
>         WHERE Program = "OSHA") ...
>