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") ...
>