SQL Server : Delete rows based on foreign key-CodePudding

I'm facing a problem. Let say I have 3 tables :

A [ID_A]
B [ID_A, ID_C]
C [ID_C]

A classical many to many relationship.

I want to delete a row in C, and all the A associated WHERE the A are only associated with C. To be clear :

[a1, a2, a3]
[[a1, c1], [a2, c1], [a1, c2]
[c1, c2]

I want to delete c1 and a1, not a2 because a2 is associated with c2 too, not a3 because a3 wasnt associated with c1.

The problem I'm facing is an order problem.

I can't delete B first, because if I delete B, I lose the informations about the association between A and c1.
I can't delete c or a first because of the foreign keys. I cant change the database to add a cascade.
I can't delete all the "no relationship a" because I want to keep a3

I'm feeling there is an obvious solution but I cant fing it...

CodePudding user response：

As mentioned by @SOS in the comments, you can use the OUTPUT clause when deleting from B to capture any relevant IDs for A. You then delete any associated A records, but exclude any that are still associated to another B record:

DECLARE @tmp TABLE (ID_A);

DELETE b
FROM B b
OUTPUT deleted.ID_A
  INTO @tmp (ID_A)
WHERE b.ID_C = 'c1';

DELETE a
FROM A a
WHERE a.ID_A IN (
    SELECT t.ID_A
    FROM @tmp t
    EXCEPT
    SELECT b.ID_A
    FROM B b
);

DELETE c
FROM C c
WHERE c.ID_C = 'c1';