Databases
StackOverflow2013
Postsdbo
POT-SQL: finding a group by its members
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POT-SQL: finding a group by its members
    text
    copied!<p>Given the following two tables in SQL Server 2005:</p> <pre><code>IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'GroupItems') DROP TABLE GroupItems; CREATE TABLE GroupItems ( RowID INT IDENTITY(1,1) PRIMARY KEY , GroupID CHAR(1) , ItemID INT ); IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ItemList') DROP TABLE ItemList; CREATE TABLE ItemList ( ItemID INT PRIMARY KEY ) INSERT GroupItems ( GroupID, ItemID ) SELECT 'A', 1 UNION SELECT 'A', 2 UNION SELECT 'A', 3 UNION SELECT 'A', 4 UNION SELECT 'B', 1 UNION SELECT 'B', 2 UNION SELECT 'B', 4 UNION SELECT 'C', 1 UNION SELECT 'C', 2 UNION SELECT 'D', 1 UNION SELECT 'D', 4 UNION SELECT 'D', 5 INSERT ItemList ( ItemID ) SELECT 1 UNION SELECT 2 UNION SELECT 4 </code></pre> <p>I'm trying to find the GroupID(s) from table GroupItems where the ItemIDs are an exact match for the contents of table ItemList.</p> <p>In the sample data, the result should be Group 'B'.</p> <p>Group A is rejected because it contains an item that is not in the ItemList table.</p> <p>Group C is rejected because it doesn't contain all the items in the ItemList table.</p> <p>Group D is rejected for both reasons.</p> <hr> <p>Currently, I'm doing something like</p> <pre><code>DECLARE @ListCount INT; SELECT @ListCount = COUNT(*) FROM ItemList; SELECT GI.GroupID FROM GroupItems AS GI INNER JOIN ItemList AS IL ON IL.ItemID = GI.ItemID INNER JOIN ( SELECT GroupID FROM GroupItems GROUP BY GroupID HAVING COUNT(*) = @ListCOunt ) AS GS ON GS.GroupID = GI.GroupID GROUP BY GI.GroupID HAVING COUNT(*) = @ListCount; </code></pre> <p>This function gives the correct result that I'm looking for, however, in my production environment, the GroupItems table has hundreds of thousands of rows and thousands of unique GroupIDs. The ItemList table usually contains about a dozen rows. This function is called fairly regularly. I'm looking for a more efficient way to get the same results.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload