Return matched-pair and the non-matched pair for each ID only once

Any help would be appreciated.

I have two sample tables here.

Table A:

ID |Name
123|REG
123|ERT
124|REG
124|ACR

Table B

ID |Name
123|REG
123|WWW
124|REG
124|ADR

Here is the simple join output and I will explain my question in the comments:

*Yes — I want this row

*No — I don’t want this row

AID|Aname|BID|Bname
123|REG  |123|REG  --Yes-- Matched-pair for id '123'
123|ERT  |123|REG  --No--'REG' already had one match. 'ERT' should pair with 'WWW' for id '123'
123|REG  |123|WWW  --No--The same reason as above
123|ERT  |123|WWW  --Yes--non-matched pair for id '123'
124|REG  |124|REG
124|ACR  |124|REG
124|REG  |124|ADR
124|ACR  |124|ADR

My desired result:

AID|Aname|BID|Bname
123|ERT  |123|WWW
123|REG  |123|REG
124|REG  |124|REG
124|ACR  |124|ADR

SQL server 2017.

Thank you in advance.

---

My approach (Inspired by the post from @The Impaler)

;with CTEall as(
select A.id as AID, A.NAME as Aname, b.id as BID,b.NAME as Bname  from A
inner join B on A.id = B.id),
match as (
select A.id as AID, A.NAME as Aname, b.id as BID,b.NAME as Bname  
from A inner join B on A.id = B.id and A.NAME = B.NAME)
select *
from CTEall 
where Aname not in (select Aname from match where AID = BID) 
and Bname not in (select Aname from match where BID = AID)
union all
select * from match
order by 1

Answer

Often when you think about the logic you want in a different way, the answer (or at least AN answer) becomes obvious.

I am thinking of your logic this way:

JOIN Table A to Table B such that A.ID=B.ID (always) AND EITHER A.Name=B.Name OR A.Name doesn’t have a Match in B, and B.Name doesn’t have a match in A.

This logic is pretty easy to express in SQL

WHERE a.ID=b.ID 
AND (
  a.Name=b.Name OR (
    NOT EXISTS(SELECT * FROM TableB b2 WHERE b2.ID=a.ID AND b2.Name=a.Name)
    AND 
    NOT EXISTS(SELECT * FROM TableA a2 WHERE a2.ID=b.ID AND a2.Name=b.Name)
  )
)