Part of a T-SQL programmer's job is to translate application users' requests for information into queries. Requests commonly involve identifying rows or groups of rows that meet some criteria—say, items that share a certain relationship to another group of items. For example, sometimes you need to identify all orders that have the same order parts as another order. I got the idea for this article from a recent problem that SQL Server Magazine reader John Lombardo sent me.
Relationships Between Sets
As an introduction to the problems I discuss in this article, let's review a few concepts from Set Theory that represent relationships between sets. I use these concepts to define the criteria for the article's problems. Capital letters specify set names, numbered small letters specify set members, and curly brackets containing set members specify the sets themselves.
Set Theory describes certain relationships that can exist between sets:
- Set U is equal to set V if all of U's members exist in V and all of V's members exist in U—for example, U = {u1, u2, u3}, V = {u1, u2, u3}.
- U is a subset of V if all of U's members exist in V. When U is equal to V, then U is also a subset of V and V is also a subset of U.
- When U is a subset of V but V isn't a subset of U, U is a proper subset of V—for example, U = {u1, u2, u3}, V = {u1, u2, u3, u4}.
The tasks that I discuss in this article involve identifying groups of items, or sets, that have a certain relationship to another group of items. Let's look at an example that sets up the problems.
The Orders and OrderDetails Scenario
The scenario I use involves example Orders and OrderDetails tables, which you can create and populate in tempdb by running the script that Listing 1 shows. In these tables, I included only the columns that are relevant for this discussion—that is, the orderid column in Orders and the orderid and productid columns in OrderDetails. Each order in the Orders table might have zero or more related rows in the OrderDetails table, each containing a different product. Each order is an instance of an entity in its own right, but in this article, I refer to an order as the set of order details that belong to it.
Your application's user enters a set of products representing a new order, which your code stores in the #ProdList temporary table:
CREATE TABLE #ProdList(productid int NOT NULL PRIMARY KEY)
INSERT INTO #ProdList VALUES(2)
INSERT INTO #ProdList VALUES(3)
INSERT INTO #ProdList VALUES(4)
You receive several tasks from the marketing department that require you to identify different relationships between the new order and existing ones. Those relationships might be important for the marketing department to identify purchase patterns, to consider discounts for certain groups of products, and so on.
Task 1: P is a subset of O. Your first task is to identify the orders that contain all products in #ProdList. In set terminology, if O represents the set of order details making up an order and P represents the set of products in #ProdList, you're looking for orders for which P is a subset of O. Your query should return orders A and B.
The following query gives you the solution for the task:
SELECT orderid
FROM OrderDetails
WHERE productid IN(SELECT productid FROM #ProdList)
GROUP BY orderid
HAVING COUNT(*) = (SELECT COUNT(*) FROM #ProdList)
This code queries the OrderDetails table, filtering for the rows containing only products that exist in #ProdList. The query groups the result by orderid, then filters the groups that have the same count of products as in #ProdList. The query returns only the orders that contain all the products from #ProdList.
Prev. page
[1]
2
next page 
