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