Here is an example of what I'm asking:

Detail Table:

HeaderId | DetailId
    1          100
    1          101
    2          100
    2          101
    3          101
    3          102
    3          103

I'm looking for a query strategy that will get me all header ids with the same set of detail ids for each.

So in the example, I would want HeaderId 1 and 2 to join together because they have the same two detail records, but 103 would not match because it has a third item in the set.

The strategy I'm taking so far is to use the STUFF technique to create a comma separate string of detail values, checksum that string, then join on the checksum result. It's seems to be working, but I'm not sure how to optimize it. Over a set of about 7000 headers, it returns in about 6-7 seconds.

Here is the query:

with Details as 
    select distinct t2.HeaderId, 
            ',' + convert(varchar(15), t2.DetailId)
            DetailTable t2
            t2.HeaderId = t2.HeaderId
        for xml path('')
    ),1,1,'')) as ChkSum
      DetailTable t1
    Details t1
        join Details t2
            on t2.ChkSum = t1.ChkSum
            and t2.HeaderId <> t1.HeaderId -- To avoid matching the same record

So - is this the right approach? And if it is, how can I optimize? Query plan doesn't have anything jumping out at me. The most weight is given to a table spool. Also, I'm trying to make this a function or proc if that helps.

Edit: I began researching relational division, and I think that is relevant here, but perhaps not in the context I am thinking. To give this more context, here is the business case I am trying to solve.

I have a set of Promotions that can have any number of UPCs in them. I'm trying to find promotions that have exactly the same set of UPCs in them. A lot of the solutions I'm seeing rely on using count(*). So - just some context for anyone looking at this. Thanks!

  • 1
    this is called Relational Division – Neil McGuigan Jan 9 '18 at 19:06
  • Ah - thank you for giving it a name - very hard to google this. So - before I dive in to research - is my approach reasonable? – IronicMuffin Jan 9 '18 at 20:03
  • What version of SQL Server? Also you say 7000 headers, how many details rows? – Martin Smith Jan 9 '18 at 20:38
  • And do you have a separate table with the HeaderIds in it so you don't have to get them all from the Details table and do DISTINCT? Especially after doing the concatenation... – Martin Smith Jan 9 '18 at 20:45

Here is a way using PIVOT and T-SQL that can work IF you have < 255 unique DetailIDs. I ran into limitation on CONCAT function (2012+) after writing the thing and stress testing it. It runs pretty well, <5 seconds on 20k headers over 40k rows, with 254 unique detail keys and a lot of matching. If your set can fit that limitation, might be worth a look.

DECLARE @sql varchar(MAX)
DECLARE @d varchar(MAX)
SET @d = stuff((
        SELECT ',' + QUOTENAME(DetailId)
        FROM (SELECT DetailId FROM DetailTable GROUP BY DetailId) d
        for xml path('')
DECLARE @tbl TABLE (H int, D varchar(254))
'SELECT HeaderId,CONCAT('+@d+') Details --'+@d+'
    SELECT HeaderId, DetailId, 1 o
    FROM DetailTable
) as s
    FOR DetailId IN ('+@d+')
) as pvt'
--Possible subquery and JOIN?

SELECT t1.H, t2.H H2  --matches
FROM @tbl t1 JOIN @tbl t2 ON t1.D = t2.D AND t1.H < t2.H

You should be able to get around the CONCAT 254 limitation by replacing the select with just @d, wrapping the PIVOT into a subquery, and stuffing out another @dj for a JOIN.


You could try using checksum_agg instead of xml concatenation.

with c as (
    h = headerid,
    g = checksum_agg(d) 
           over(partition by headerid)
  from detailtable

select distinct main, copy
from (
    main = min(l.h) over(partition by l.g), 
    copy = l.h
from c as l
) x
where x.main < x.copy

fiddle: http://sqlfiddle.com/#!6/df56a/16

with cte1 as 
select headerID, count(*) as cnt 
from Detail 
group by headerID, 

cte2 as 
select h1.headerID as ID1, h2.headerID as ID2, count(*) as cnt 
from detail h1 
join detail h2 
 on h1.headerID < h2.headerID 
and h1.DetailId = h2.DetailId 
group by h1.headerID, h2.headerID

select cte2.* 
from cte2 
join cte1 as cte1a
  on cte1a.headerID = cte2.ID1  
 and cte1a.cnt      = cte2.cnt 
join cte1 as cte1b
  on cte1b.headerID = cte2.ID2  
 and cte1b.cnt      = cte2.cnt 

I bet a full outer join and look for null would be simpler but I have not tested it. I think it would also be less efficient.


So @Neil McGuigan's comment about relational division lead me to this article.

I found the example of "Todd's Division - Dwain.C 1" to be performant and give me the results I was looking for.

This is the example from the article that I pretty much used verbatim except for field/table names:

-- Todd's Division - Dwain.C 1
SELECT j.ProjectID, s.ResourceID
FROM #ProjectTasks j
JOIN #ResourceTasks s ON j.TaskID = s.TaskID
    SELECT ProjectID, c_res=COUNT(*)
    FROM #ProjectTasks
    GROUP BY ProjectID
) c ON j.ProjectID = c.ProjectID
GROUP BY j.ProjectID, ResourceID
HAVING COUNT(*) = MAX(c_res)
ORDER BY j.ProjectID, ResourceID;

Thank you for the rest of the suggestions, they didn't quite get me there, but ultimately I found a solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.