CODEWITHSUNDEEP
sql-serveralgorithmt-sqlgroup-by
BrandonAGr
BrandonAGr

Reputation: 6017

TSql equality on groups of rows

I have a table that contains information on groups. There can be any number of members in a group. There is a group identifier and then an element identifier. I want to be able to in a single statement determine whether or not a given set exists in the table

@groupTable is an example of the data that already exists in the database

@inputData is the data that I want to see if it already exists in @groupTable

declare @groupData table
(
    groupIdentifier int,
    elementIdentifier uniqueidentifier
)

insert into @groupData values
(1, 'dfce40b1-3719-4e4c-acfa-65f728677700'),
(1, '89e7e6be-cee8-40a7-8135-a54659e0d88c')

declare @inputData table
(
    tempGroupIdentifier int,
    elementIdentifier uniqueidentifier
)

insert into @inputData values
(42, 'dfce40b1-3719-4e4c-acfa-65f728677700'),
(42, '89e7e6be-cee8-40a7-8135-a54659e0d88c'),
(55, 'dfce40b1-3719-4e4c-acfa-65f728677700'),
(55, '2395a42c-94f4-4cda-a773-221b26ea5e44'),
(55, 'f22db9df-a1f4-4078-b74c-90e34376eff6')

Now I want to run a query that will show the relationship of the sets, showing which groupIdentifier is associated with which tempGroupIdentifier. If there is no matching set then I need to know that too.

desired output:
groupIdentifier, tempGroupIdentifier
1, 42
null, 55

Does anyone any suggestions on how to approach this problem?

I could probably pivot the rows and concat all elementIdentifiers into a giant string for each group that then do equality on, but that doesn't seem like a good solution.

Upvotes: 1

Views: 145

Answers (2)

gbn
gbn

Reputation: 432491

SELECT DISTINCT
    T1.tempgroupIdentifier, T2.GroupIdentifier
FROM
    (
    SELECT
        COUNT(*) OVER (PARTITION BY tempgroupIdentifier) AS GroupCount,
        ROW_NUMBER() OVER (PARTITION BY tempgroupIdentifier ORDER BY elementIdentifier) AS GroupRN,
        tempgroupIdentifier, elementIdentifier
    FROM
        @inputData
    ) T1
    LEFT JOIN
    (
    SELECT
        COUNT(*) OVER (PARTITION BY GroupIdentifier) AS GroupCount,
        ROW_NUMBER() OVER (PARTITION BY GroupIdentifier ORDER BY elementIdentifier) AS GroupRN,
        GroupIdentifier, elementIdentifier
    FROM
        @groupData
    ) T2 ON T1.elementIdentifier = T2.elementIdentifier AND 
                      T1.GroupCount = T2.GroupCount AND 
                      T1.GroupRN = T2.GroupRN

Edit: this will also deal with the same value in a given set

Upvotes: 3

ImplexOne
ImplexOne

Reputation: 549

   SELECT 
        (
        CASE WHEN matchCount = gdCount AND matchCount = idCount 
            THEN groupIdentifier 
            ELSE NULL 
        END) groupIdentifier, 
        cj.tempGroupIdentifier 
    FROM
    (
    SELECT gd.groupIdentifier, id.tempGroupIdentifier, COUNT(1) matchCount
    FROM @groupData gd 
    CROSS JOIN @inputData id
    WHERE id.elementIdentifier = gd.elementIdentifier 
    GROUP BY gd.groupIdentifier, id.tempGroupIdentifier) as cj
    CROSS APPLY (SELECT COUNT(groupIdentifier) from @groupData gdca WHERE gdca.groupIdentifier = cj.groupIdentifier) as gdc(gdCount)
    CROSS APPLY (SELECT COUNT(tempGroupIdentifier) from @inputData idca WHERE idca.tempGroupIdentifier = cj.tempGroupIdentifier) as idc(idCount)

Upvotes: 1

Related Questions