select OperatorID, FirstName, LastName, Phone1
from Operator
where OperatorID not in (select OperatorID from OperatorJobs where EndDate
is null)
order by OperatorID
The "NOT IT" but me in the right track...
Thanks guys!
Jacob
At 11:35 AM 5/17/2004, you wrote:
>This should work without the complications of the JOIN stuff.
>
>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
>
> _____
>
>----------
>[<http://www.houseoffusion.com/lists.cfm/link=t:6>Todays Threads]
>[<http://www.houseoffusion.com/lists.cfm/link=i:6:1936>This Message]
>[<http://www.houseoffusion.com/lists.cfm/link=s:6>Subscription]
>[<http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=649.569.6>Fast
> Unsubscribe] [<http://www.houseoffusion.com/signin/>User Settings]
>
>----------
><http://www.houseoffusion.com/banners/view.cfm?bannerid=40>
>[]
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
