Need Help with SQL Subquery
- by Pete Augello
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]
