If I understand you, your table is of OrderItems... One record for each line
item on every order... If that's the case, then the query would look like th
is
Select Distinct OrderID
From OrderItems I
Where Exists
(Select * From OrderItems
Where OrderID = I.OrderID
And Item = 'A')
And Exists
(Select * From OrderItems
Where OrderID = I.OrderID
And Item = 'B')
or...
Select Distinct A.OrderID
From OrderItems A
Join OrderItems B
On B.OrderID = A.OrderID
Where A.Item = 'A'
And B.Item = 'B'
"Larry Woods" wrote:
> I have a situation where I have multiple records, let's say ORDERS, and I
> have a record for each line item included in an order. Now, I want to fin
d
> all ORDERS that includes item A and item G, for example. An ORDER could
> include many additional items but the ORDER #'s that I want returns must
> include AT LEAST item A and G.
> How do I do this?
> TIA,
> Larry Woods
>
>Thanks. I'm going with the join for now. But, here is the next question:
The output of the SELECT is a list of OrderID's. Assuming we have
'customerID' in the Order table AND we have 'customerState' in the Customer
table, how do I "expand" the selection to only give me customers from
California (value="CA"), for example?
Customer Table Order Table
orderID <<<< (SELECT
OrderID...etc.)
customerID <<<<<< customerID (foreign key)
customerState
Again, TIA,
Larry Woods
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:02D90644-CC28-4B8E-9AEF-41E6C30EB034@.microsoft.com...
> If I understand you, your table is of OrderItems... One record for each
line
> item on every order... If that's the case, then the query would look like
this
> Select Distinct OrderID
> From OrderItems I
> Where Exists
> (Select * From OrderItems
> Where OrderID = I.OrderID
> And Item = 'A')
> And Exists
> (Select * From OrderItems
> Where OrderID = I.OrderID
> And Item = 'B')
> or...
> Select Distinct A.OrderID
> From OrderItems A
> Join OrderItems B
> On B.OrderID = A.OrderID
> Where A.Item = 'A'
> And B.Item = 'B'
>
> "Larry Woods" wrote:
>
I
find|||On Sat, 12 Mar 2005 04:51:46 -0700, Larry Woods wrote:
>Thanks. I'm going with the join for now. But, here is the next question:
>The output of the SELECT is a list of OrderID's. Assuming we have
>'customerID' in the Order table AND we have 'customerState' in the Customer
>table, how do I "expand" the selection to only give me customers from
>California (value="CA"), for example?
>Customer Table Order Table
> orderID <<<< (SELECT
>OrderID...etc.)
> customerID <<<<<< customerID (foreign key)
> customerState
>Again, TIA,
Hi Larry,
SELECT o.OrderID
FROM Orders AS o
INNER JOIN Customers AS c
ON c.CustomerID = o.CustomerID
INNER JOIN (SELECT OrderID
FROM OrderItems
WHERE Item IN ('A', 'B')
GROUP BY OrderID
HAVING COUNT(*) = 2) AS oi
ON oi.OrderID = o.OrderID
WHERE c.CustomerState = 'CA'
(untested - see www.aspfaq.com/5006 for the steps required to get a
tested solution)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql
