Find one match set in multiple sets

Question

I have a table (@ t1) that contains several sets. I want to find the perfect match for @ t2 in @ t1.

In this example, the desired result is 1.

(set 1 works well, set 2 contains three elements, while @ t2 contains only two elements, set 3 contains fewer elements than @ t2, set 4 contains NULL elements that are not allowed in @ t2, and the set 5 contains the correct number of elements, but one of the elements is not equal.)

DECLARE @t1 TABLE (id INT, data INT); DECLARE @t2 TABLE (data INT PRIMARY KEY); INSERT INTO @t1 (id, data) VALUES (1, 1), (1, 2), (2, 1), (2, 2), (2, 3), (3, 1), (4, NULL), (4, NULL), (5, 1), (5, 3); INSERT @t2 (data) VALUES (1), (2); 

I have a request that can be executed, but it looks kind of pathetic too.

 WITH t1 AS ( SELECT id, data FROM @t1 WHERE data IS NOT NULL ), t1_count AS ( SELECT id, RCount = COUNT(*) FROM @t1 WHERE data IS NOT NULL GROUP BY id ) SELECT t1.id FROM t1 JOIN t1_count ON t1.id = t1_count.id FULL JOIN @t2 t2 ON t1.data = t2.data WHERE t1_count.RCount = (SELECT RCount = COUNT(*) FROM @t2) GROUP BY t1.id HAVING COUNT(t1.data) = COUNT(t2.data); 

EDIT (comment by GarethD):

 WITH t1 AS ( SELECT id, data, RCount = COUNT(*) OVER(PARTITION BY id) FROM @t1 WHERE data IS NOT NULL ) SELECT t1.id FROM t1 FULL JOIN @t2 t2 ON t1.data = t2.data WHERE t1.RCount = (SELECT RCount = COUNT(*) FROM @t2) GROUP BY t1.id HAVING COUNT(t1.data) = COUNT(t2.data); 

Answer 1

What you want is called the Exact Relational Division . Unfortunately, SQL Server does not have its own statement for this, but this is a well-documented problem. One possible solution (an idea taken from Joe Celco 's article ) is to compare values ​​similar to what you are already doing:

 SELECT t1.id FROM @t1 AS t1 LEFT JOIN @t2 AS t2 ON t1.data = t2.data GROUP BY t1.id HAVING COUNT(t1.data) = (SELECT COUNT(data) FROM @t2) AND COUNT(t2.data) = (SELECT COUNT(data) FROM @t2); 

Note what are needed as HAVING comparisons:

• The first ensures that t1 has exactly the required number of rows and
• the second guarantees that these lines contain only the values ​​from t2 (otherwise t2.data will be NULL via LEFT JOIN. Recall that COUNT (x) takes into account only non-zero values ​​of x).

Answer 2

One way to solve this problem is to combine the values ​​for each ID and the values ​​in the second table and compare them. You can also apply the concatenation order.

For example, the following code combines the values ​​from a second table:

 DECLARE @Test VARCHAR(MAX) = ( SELECT ',' + data FROM @t2 ORDER BY data FOR XML PATH(''), TYPE ).value('.', 'VARCHAR(MAX)') SELECT @test -- 12 

and the following will be done for the first table:

 SELECT id ,( SELECT ',' + data FROM @t1 WHERE id = t1.id ORDER BY data FOR XML PATH(''), TYPE ).value('.', 'VARCHAR(MAX)') FROM @t1 t1 GROUP BY id 

You can easily filter the values ​​using the where clause:

 SELECT * FROM ( SELECT id ,( SELECT ',' + data FROM @t1 WHERE id = t1.id ORDER BY data FOR XML PATH(''), TYPE ).value('.', 'VARCHAR(MAX)') data FROM @t1 t1 GROUP BY id ) DS WHERE data = @test 

---

Concatenating values ​​in T-SQL does not look nice , but you can apply the aggregation concatenation function using the SQL CLR (check this article ), and then you will have something like this:

 DECLARE @Test VARCHAR(MAX); SELECT @Test = [dbo].[Concatenate] (data) FROM @t2 SELECT id FROM @t1 GROUP BY id HAVING [dbo].[Concatenate] (data) = @Test 

Answer 3

Maybe this seems simpler to you?

 SELECT dat.id FROM ( SELECT t1.id as id, t2.data as data2, sourcecount.cnt as scnt, dataCount.cnt as dcnt, COUNT(*) OVER(PARTITION BY t1.id) as mcnt FROM @t1 as t1 INNER JOIN (SELECT t.id, COUNT(*) as cnt FROM @t1 as t GROUP BY t.id) as sourceCount ON t1.id = sourceCount.id INNER JOIN @t2 as t2 ON t1.data = t2.data CROSS JOIN (SELECT COUNT(*) as cnt FROM @t2) as dataCount ) as dat WHERE dat.scnt = dat.dcnt AND dat.mcnt = dat.dcnt GROUP BY dat.id 

Which, by the way, gives the same execution plan as yours, but perhaps a little more readable.

Regards, Ionic