Need Help with SQL Subquery
I am trying to write a query that will return all orders that only have a Subscription included. It is easy enough to write a query that includes all Orde开发者_JAVA技巧rs with Subscriptions, another that includes all orders without a Subscription and then compare them with an unmatched query.
But I don't want to have to store Queries in my Access database, I prefer to have it all in my ASP code, and I can't get this to work with just one complex query.
Here are samples of what works if I store them:
Query1
SELECT tblOrders.OrderID, tblOrderItems.ProductID
FROM tblOrders INNER JOIN tblOrderItems ON tblOrders.OrderID = tblOrderItems.OrderID
WHERE ((Not ((tblOrderItems.ProductID)>=12 And (tblOrderItems.ProductID)<=15)));
Query2
SELECT tblOrders.OrderID, tblOrderItems.ProductID
FROM tblOrders INNER JOIN tblOrderItems ON tblOrders.OrderID = tblOrderItems.OrderID
WHERE ((((tblOrderItems.ProductID)>=12 And (tblOrderItems.ProductID)<=15)));
Query3
SELECT Query2.OrderID, Query2.ProductID
FROM Query2 LEFT JOIN Query1 ON Query2.OrderID = Query1.OrderID
WHERE (((Query1.OrderID) Is Null));
So, my question is 'how do I write Query3 so that it doesn't refer to Query1 or Query2?' or, am I missing some other way do do this?
Thanks, Pete peteaugello@verizon.net
Assumptions
- ProductID between 12 and 15 refers to subscriptions.
- You are looking for all orders with only subscriptions and no other product types.
How about something like this:
SELECT O.OrderID, TOI.ProductID
FROM tblOrders O
INNER JOIN tblOrderItems TOI ON (O.OrderID = TOI.OrderID)
WHERE (TOI.ProductID between 12 and 15) AND
NOT EXISTS (SELECT *
FROM tblOrderItems TOI2
WHERE (NOT TOI2.ProductID between 12 and 15) AND
(TOI2.OrderID=O.OrderID)
)
If you do not want to worry about having a join, here is a way to do it with a pivot table.
select OrderID,
sum(case productID between 12 and 15 then 1 else 0 end) HAS_SUBSCRIPTION,
sum(case productID between 12 and 15 then 0 else 1 end) HAS_OTHER
FROM tblOrderItems
GROUP BY OrderID
HAVING HAS_SUBSCRIPTION > 0 and HAS_OTHER = 0;
精彩评论