Need Help with SQL Subquery

Posted by Pete Augello on Stack Overflow See other posts from Stack Overflow or by Pete Augello
Published on 2010-03-12T20:34:28Z Indexed on 2010/03/12 20:37 UTC
Read the original article Hit count: 411

Filed under:
|
|

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]

© Stack Overflow or respective owner

Related posts about access

Related posts about sql