select operatorID,
FirstName,
LastName,
Phone1
from Operator
where operatorid in (select OperatorID
from OperatorJobs
where EndDate is not null)
order by OperatorID
Ray Thompson
Tau Beta Pi ( <http://www.tbp.org/> www.tbp.org)
The Engineering Honor Society
865-546-4578
_____
From: Jacob [mailto:[EMAIL PROTECTED]
Sent: Monday, May 17, 2004 2:18 PM
To: SQL
Subject: Stuck on a SQL query...
Okay.. I have been stuck on this query for about an hour now. It can not
be that hard, can it? Only query left in this project and I am having a
brain ####...
Two tables:
Operator
OperatorJobs
TABLE - Operator:
OperatorID (numeric) PK
FirstName (char)
LastName (char)
Phone (char)
OperatorID FirstName LastName Phone
======== ======== ======= ===========
1001 Barry Bonds 555-555-1212
1002 Babe Ruth 111-222-3333
1003 Mickey Mantle 222-333-4444
1004 Cy Young 999-000-3333
1005 Pete Rose 777-444-1234
TABLE - OperatorJobs:
OperatorID (numeric)
JobID (numeric)
StartDate (datetime)
EndDate (datetime)
OperatorID JobID StartDate EndDate
======== ==== ======= ======
1001 1001 5/1/2004 5/2/2004
1002 1001 5/1/2004 <NULL>
1001 1002 5/3/2004 <NULL>
1003 1003 5/4/2004 5/5/2004
1004 1003 5/4/2004 <NULL>
What I am trying to do is pull all Operators from Operator table that are
not on a job. So, if their OperatorID has a null EndDate in OperatorJobs,
I do not want those Operators.
Based on above, I should get the following OperatorIDs
1003
1005
Something like this:
select a.operatorID, a.FirstName, a.LastName, a.Phone1
from Operator a, OperatorJobs b
where a.OperatorID = b.OperatorID and a.OperatorID <> (select OperatorID
from OperatorJobs where EndDate is not null)
order by a.OperatorID
That does not work because subquery has more than one value.
I have played around with the query and I am at a loss...
TIA
Jacob
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
