Query order that contains certain items only 1

[scotttom]

So I'm stumped as to how to get a list of orders that contain a certain combination, and only that combination, of items.

I need the exact combination of items. Unfortunately if my query has 5 items I'm getting orders that have one of the items included and I don't want those.

Hmmmm.....

Thanks in advance for any help!


ST

 

[Andrzejek]

Could you show a sample of your data representing your problem and what you want to get out of it?
And your SELECT statement (even if it doesn't work at this time)

Have fun.

---- Andy

 

[scotttom]

Sure.....

Boiled down it is this...

tblOrders.....

OrderID Desc
1 Bread, Beer and Milk
2 Bread
3 Beer


tblDetails......

DetailID Item OrderID
1 Bread 1
2 Beer 1
3 Milk 1
4 Bread 2
5 Beer 3


Ok and my simplified query is....

SELECT tblOrders.OrderID
FROM tblOrders INNER JOIN tblDetails ON tblOrders.OrderID =tblDetails.OrderID
WHERE (((tblDetails.Item) In ("Bread","Beer","Milk")));


What I want to see is ONLY order 1 returned but I'm getting all three.

Thanks again,

ST

 

[Andrzejek]

Maybe....

SELECT tblOrders.OrderID
FROM tblOrders INNER JOIN tblDetails ON 
tblOrders.OrderID =tblDetails.OrderID
WHERE (tblDetails.Item) In ("Bread","Beer","Milk")[blue]
AND tblDetails.OrderID = 1[/blue]

Have fun.

---- Andy

 

[PWise]

Select orderid
from orders
inner join tblDetails as Bread
on bread.orderid=orders.orderid
inner join tblDetails as Milk
on milk.orderid=orders.orderid
inner join tblDetails as Beer
on Beer.orderid=orders.orderid
Where Beer.Item = "Beer"
And Milk.Item = "Milk"
And Bread.Item = "Bread"

 

[scotttom]

Right. That would give me OrderID 1, but I wouldn't know what orderID to put in the WHERE clause because that's what I'm asking for.

I'm asking for the orderID that has Bread, Beer and milk as details.

Any other thoughts?

 

[scotttom]

BRILLIANT PWISE!!

I owe you another beer!

 

[scotttom]

OK PWise I spoke too soon....

I have one more nuance...

What if I wanted orders that ONLY have beer.

Using your select statement if I search only for beer I get order 1 and 3 returned. In that case I'd want only 3.

Make sense?

Thanks again,

ST