Re: Simple Query problem
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 02/14/05
- Next message: Magnus Broman: "Formatting numeric fields in select-clause"
- Previous message: Wes: "Simple Query problem"
- In reply to: Wes: "Simple Query problem"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 14 Feb 2005 14:38:16 +0100
On Mon, 14 Feb 2005 03:29:24 -0800, Wes wrote:
>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.
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
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Magnus Broman: "Formatting numeric fields in select-clause"
- Previous message: Wes: "Simple Query problem"
- In reply to: Wes: "Simple Query problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
