# Finding Missing Inverse Relationships

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

• This should give you some ideas. – mustaccio Jan 20 '20 at 18:43

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

• 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 – Dominique Boucher Jan 20 '20 at 19:33
• Perfect, so simple when you know how eh Randi!? :) Never used EXCEPT before, that's a nifty tool for the belt! Thanks so much. David. – david Jan 21 '20 at 9:05
• @david True! To be honest I also had to take a few takes to think about the inverse relationship. Have a good day :). – Randi Vertongen Jan 21 '20 at 9:10
• @DominiqueBoucher You're free to copy that into the "Your Answer" box. – Michael Green Jan 21 '20 at 10:35

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.

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
``````