FuturShoc


I'm trying to write a very specific subquery.

My table data looks like this.

jobname1 complete destinationA
jobname1 complete destinationB
jobname1 not-complete destinationC
jobname2 complete destinationA
jobname2 complete destinationB
jobname2 complete destinationC
jobname3 complete destinationA
jobname3 complete destinationB
jobname3 complete destinationC

The result I need is the job name of all unique job names whose records are ALL complete. So, in the case above, I need the names of jobname2 and jobname3.

Here's what I have so far, but itt is so far returning no results:

Select jobname from table where NOT EXISTS (Select * from table where table.job_status != 'complete')

Can somone nudge me as to why my query isn't returning any results or where I may be going wrong



Re: Subquery Question

Jens K. Suessmeyer


Hi,

what about

SELECT Jobname FROM SomeTable ST
WHERE EXISTS
(
SELECT * FROM SomeTable S1 WHERE S1.jobName = ST.Jobname AND destination = 'destinationA'
) AND
EXISTS
(
SELECT * FROM SomeTable S2 WHERE S1.jobName = ST.Jobname AND destination = 'destinationB'
) AND
EXISTS
(
SELECT * FROM SomeTable S3 WHERE S1.jobName = ST.Jobname AND destination = 'destinationC'
)

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---






Re: Subquery Question

Robert Wishlaw

SELECT DISTINCT jobname FROM table WHERE jobname <> (SELECT jobname FROM table WHERE job_status = 'not-complete')

Robert Wishlaw






Re: Subquery Question

DannoCoy

Try this...

SELECT DISTINCT jobname FROM jobstatustable WHERE jobname NOT IN (SELECT jobname FROM jobstatustable WHERE jobstatus = 'not-complete')






Re: Subquery Question

DannoCoy

Robert Wishlaw wrote:

SELECT DISTINCT jobname FROM table WHERE jobname <> (SELECT jobname FROM table WHERE job_status = 'not-complete')

Robert Wishlaw

Robert, just an FYI... this query will only work if ONLY one job name is returned in the subselect. If more than one job was marked as "not-complete", the subquery will fail because multiple rows are returned.

Errror Message:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.