sql server mseq: Sample table as follows

Order ID Stock Code Status
--------- ------- -------
203 STK1 3
203 STK2 2
203 STK4 3
204 STK1 3
204 STK5 3
205 STK2 2
205 STK1 3

I want a query that will return the order id when ALL
ORDER LINES are of Status 3.

i.e. from the sample above, the only table that has ALL
LINES with a status of 3 is Order 204.

Thanks,

[quoted text, click to view]

Hi Wes,

SELECT OrderID
FROM MyTable AS a
WHERE Status = 3
GROUP BY OrderID
HAVING COUNT(*) = (SELECT COUNT(*)
FROM MyTable AS b
WHERE b.OrderID = a.OrderID)

or

SELECT DISTINCT OrderID
FROM MyTable AS a
WHERE NOT EXISTS (SELECT *
FROM MyTable AS b
WHERE b.OrderID = a.OrderID
AND b.Status <> 3)

or (probably the quickes, though I didn't test it)

SELECT OrderID
FROM MyTable AS a
GROUP BY OrderID
WHERE MIN(Status) = 3
AND MAX(Status) = 3

Best, Hugo
--