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)) + '.' +

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

StackExchange Database Administrators Q#257800, answer score: 6