Need Help with SQL Subquery

Question

Hey: 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 Orders 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 [email protected]

Answer 1

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)
                 )

Answer 2

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;