Re: New to SQL -- Help?

In general when the decision to select a row is based on information in a separate row(s) in another (or the same) table, you're going to need a subquery.

You could do something like:

select emp_id, ...
from tests A
where exists (select 1

                     from tests 
                     where A.emp_id = emp_id 
                     and course = 10)
and exists (select 1
                 from tests
                 where A.emp_id = emp_id
                 and course = 20)

repeat the 'and exists' subquery for each course you want.

Is is possible for an employee to take the same class more than once?

If not, you could get away with:

select emp_id
from tests
where course IN (10,20,30) <-- list the ones you want here group by emp_id
having count(*) >= 3 <-- the number of courses in the above list

But if an employee has taken course 10 three times, they'll be selected.

There's probably an easier way. I'm not a SQL guru.

>"D.A.Pratte" <dpratte_at_worldnet.att.net> wrote:
>OK, I'm very new to SQL and sure could use a pointer. I am utterly
>confused. A million thanks.
>
>An abstraction of the problem I have follows:
>
>Suppose I have two tables in my database. The first is just an employee
>summary (Last Name, First Name, Employee ID, etc.). The Employee ID is
>the primary key.
>
>The second table is multiple rows but two columns, Employee ID and a 2
>digit ( integer data type ) codes corresponding to course training
>they've received. Conceivably each Employee ID may have as many as 24
>rows as there are 24 different courses. Now I'm trying to develop a
>query which allows me to identify employees who have completed (say)
>course 1, AND course 2, AND course 10, AND course N ... etc. (you get
>the idea).
>
>I've played around with inner joins, subqueries to the point of
>confusion. I can do this with OR logic but not with AND.
>
>
>SELECT DISTINCTROW Employees.FirstName, Employees.MiddleInit,
>Employees.LastName, Employees.DepartmentID, Tests.EmployeeID
>
>FROM Employees INNER JOIN Tests ON Employees.EmployeeID =
>Tests.EmployeeID;
>
>?????WHERE?????????
>
>Might someone point me in the right direction? Thanks a million,
Received on Sun Feb 23 1997 - 00:00:00 CET