Getting started with SQL Server
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.
SELECT DISTINCT jobname FROM table WHERE jobname <> (SELECT jobname FROM table WHERE job_status = 'not-complete')
Robert Wishlaw
Try this...
SELECT
DISTINCT jobname FROM jobstatustable WHERE jobname NOT IN (SELECT jobname FROM jobstatustable WHERE jobstatus = 'not-complete')
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.