By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3609642 fiddles created (47238 in the last week).
CREATE TABLE dbo.bla(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
PartyId int,
OtherPartyId int,
RelType int,
RelStatus int);
✓
hidden batch(es)
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
6 rows affected
hidden batch(es)
SELECT PartyId , OtherPartyId, RelType , RelStatus
FROM dbo.Bla
EXCEPT
SELECT OtherPartyId , PartyId, RelType , RelStatus
FROM dbo.Bla;
PartyId
OtherPartyId
RelType
RelStatus
1111
2211
2
2
3344
4444
1
2
4444
3344
2
2
5555
2224
1
2
…
hidden batch(es)
SELECT 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
);
