6

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 at 18:43
6

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 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 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 at 9:10
  • @DominiqueBoucher You're free to copy that into the "Your Answer" box. – Michael Green Jan 21 at 10:35
5

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.