Finding Missing Inverse Relationships

Question

I'm struggling with a query which I feel should be straightforward but I just can't get my head around it.

We have a single table that holds relationships between different parties. The table SHOULD contain a row for each relationship. However, we've noticed that some relationships are missing and we want to rebuild those. Essentially there should be two rows per relationship, the relationship and the inverse.

So, a healthy table should look like this...

Id | PartyId | OtherPartyId | RelType | RelStatus
1   1111      2211            1         1
2   2211      1111            1         1
3   3344      4444            1         2
4   4444      3344            1         2

But, we have a situation like this...

Id | PartyId | OtherPartyId | RelType | RelStatus
1   1111      2211            1         1
2   2211      1111            1         1
3   3344      4444            1         2
4   5555      2224            1         2
5   2224      5555            1         2

The inverse of the relationship between party 3344 and 4444 is missing, so I want to return that row only so that I can insert the inverse relationship.

The 'id' column is immaterial as it is only an identity column, but RelType and RelStatus are important as these need to be the same for the inverse relationship - example being that there could be a relationship that DOES exist between 4444 and 3344 but it is of a different type and status so we should ignore that (unless of course it has a missing inverse too!).

I tried to create a composite key system to try and identify the missing rels but I found it brought back duplicates in some instances:

            SELECT
        CASE WHEN [PartyId] < [OtherPartyId] 
            THEN CAST([OtherPartyId] AS NVARCHAR(50)) + '.' + CAST([PartyId] AS NVARCHAR(50)) + '.' + CAST([RelType] AS NVARCHAR(50)) +  '.' + CAST([RelStatus] AS NVARCHAR(50))  
            ELSE CAST([PartyId] AS NVARCHAR(50)) +  '.'  + CAST([OtherPartyId] AS NVARCHAR(50)) +  '.' + CAST([RelType] AS NVARCHAR(50)) +  '.' + CAST([RelStatus] AS NVARCHAR(50)) 
        END AS CompKey
        INTO #partyinvs
        FROM PartyRelationships;

        SELECT  CompKey
        FROM    #partyinvs
        GROUP BY CompKey
        HAVING  COUNT(*) < 2

I hope this makes sense?

Thanks, David

Randi Vertongen · Accepted Answer · 2020-01-21T09:09:12.350

Using EXCEPT could help you write this query.

In short, if any rows of the first select do not have matches with the inverse + the correct RelType & RelStatus , return them.

SELECT PartyId , OtherPartyId, RelType , RelStatus   
FROM dbo.Table
EXCEPT 
SELECT OtherPartyId , PartyId, RelType , RelStatus   
FROM dbo.Table;

DB<>Fiddle

Remember that if there are duplicate rows (E.G. 3 rows with an inverse relationship) these will not be returned.

Example

DDL

CREATE TABLE dbo.bla(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                     PartyId int,
                     OtherPartyId int,
                     RelType int,
                     RelStatus int);

DML

INSERT INTO dbo.Bla(PartyId , OtherPartyId , RelType , RelStatus)
VALUES
(1111,2211           ,1       ,1),
(2211,1111           ,1       ,1),
(3344,4444           ,1       ,2),
(5555,2224           ,1       ,2),
(4444,3344           ,2      ,2),
(1111,2211           ,2      ,2);-- different type or status

Query

SELECT PartyId , OtherPartyId, RelType , RelStatus   
FROM dbo.Bla
EXCEPT 
SELECT OtherPartyId , PartyId, RelType , RelStatus   
FROM dbo.Bla;

Result

PartyId OtherPartyId    RelType RelStatus
1111    2211            2         2
3344    4444            1         2
4444    3344            2         2
5555    2224            1         2

Another solution could be using NOT EXISTS, E.G. When you also need the Id field.

SELECT Id,PartyId , OtherPartyId, RelType , RelStatus   
FROM dbo.Bla b
WHERE NOT EXISTS
(
SELECT * 
FROM dbo.Bla b2
where b.OtherPartyId = b2.PartyId
AND   b.PartyId = b2.OtherPartyId
AND b.RelType = b2.RelType
AND b.RelStatus = b2.RelStatus
);

DB<>Fiddle

score 5 · Answer 2 · answered Jan 20 '20 at 20:58

The most common version of the query to do this would be something like:

INSERT INTO PartyRelationships (
    PartyId, 
    OtherPartyId, 
    RelType, 
    RelStatus
    )
SELECT pr.OtherPartyID, 
    pr.PartyId,
    pr.RelType,
    pr.RelStatus
FROM papr
LEFT JOIN PartyRelationships pri
    ON pr.PartyId = pri.OtherPartyId
    AND pr.otherPartyId = pri.PartyId
    AND pr.RelType = pri.RelType
    AND pr.RelStatus = pri.RelStatus
WHERE pri.partyId is null

The EXCEPT example by @randi-vertongen is also a method to do this, and may be more efficient. And one other method that is not an example of the above is the MERGE method

MERGE INTO PartyRelationships AS pr
USING PartyRelationships AS pri
    ON pr.PartyID = pri.OtherPartyId
    AND pr.OtherPartyId = pri.PartyId
    AND pr.RelType = pri.RelType
    AND pr.RelStatus = RelStatus
WHEN NOT MATCHED THEN
    INSERT(PartyId, OtherPartyId, RelType, RelStatus)
    VALUES(pri.OtherPartyId, pri.PartyId, pri.RelType, pri.RelStatus);

This is basically the same as the first example, just using the MERGE syntax. However, this operation is a self-merge of a sort, so this makes logical sense to me.

Whichever option you choose will work for what you have requested.

score 0 · Answer 3 · edited Jan 22 '20 at 16:16

0

You could also do it with a left join :

select a.PartyId, a.OtherPartyId, a.relType, a.RelStatus  
from Bla a left 
join bla b on a.PartyId = b.OtherPartyIdand a.OtherPartyId=b.PartyId 
   and a.RelType=b.RelTypeand a.RelStatus=b.RelStatus  
where b.RelTypeis null

edited Jan 22 '20 at 16:16

LowlyDBA - John M

11,059
11
45
63

answered Jan 22 '20 at 16:08

Dominique Boucher

3,287
11
27

Finding Missing Inverse Relationships

3 Answers3