A Different Setup

Last month, in "Set Members and Relationships" (InstantDoc ID 38515), I presented a group of problems that dealt with identifying sets that have certain relationships with other sets. I mentioned that in relational algebra, the problems I discussed involve relational division. Using a common Orders-OrderDetails scenario, I showed how to find existing orders that had the same products as a new order entered into the system and other variations of the problem. I use these same scenarios again in this article's examples.

You'll need to first run the code I provided in last month's Listing 1 to create the Orders and OrderDetails tables and populate them with sample data. (You can download the code at InstantDoc ID 38515.) Orders contains one row for each order, and OrderDetails contains zero or more rows for each order, each with a different product. You also need to run the following code to create and populate the #ProdList table, which represents a new order containing a list of products:

CREATE TABLE #ProdList(productid int 
NOT NULL PRIMARY KEY)
INSERT INTO #ProdList VALUES(2)
INSERT INTO #ProdList VALUES(3)
INSERT INTO #ProdList VALUES(4)

Last month, I used set terminology to present five variations of a problem in which you had to find orders that had certain relationships with #ProdList. I used the letter O to represent the set of products making up an order and the letter P to represent the set of products in #ProdList. The five problems were:

1. P is a subset of O: Find the orders that contain all products that appear in #ProdList and possibly other products as well.
2. P is equal to O: Find the orders that contain all of and only the products that appear in #ProdList.
3. P is a proper subset of O: Find the orders that contain all products that appear in #ProdList and at least one other product as well.
4. O is a subset of P: Find the orders for which all products appear in #ProdList, and return those even if #ProdList contains other products.
5. O is a proper subset of P: Find the orders for which all products appear in #ProdList, and return those only if #ProdList contains at least one other product.

Last month's solutions used joins and aggregate functions. This time, let's use only the EXISTS() predicate and correlated subqueries, in which the subqueries refer to columns of an outer query. You might not find any performance improvements in this month's solutions, but they require "outside-the-box" thinking that might prove handy one day.

I also present some problems in which you need to identify sets that meet certain collective-member criteria such as finding orders that contain products 1 and 3 or products 2 and 4. To untangle such problems, you need to examine data that's spread across different table rows.

Without further ado, let's start with the first problem—remember, no aggregations! As a hint, a big part of solving a problem is how you phrase the request.

Task 1: P is a subset of O. Instead of phrasing the problem as "find the orders that contain all products that appear in #ProdList and possibly other products as well," think of it as "find the orders for which no product exists in #ProdList that doesn't exist in the order's details." The tough part here is to train yourself to think this way—to fully understand such a sentence with its subtle possible variations. Once you do, the translation to T-SQL is simple, as Listing 1 shows. Orders A and B meet the criteria because both contain all products from #ProdList.