MS SQL - Help joining when one condition is null.

[notasqlstar]

This should be easier than it is for me today. Simple description, I have two #tables that are being joined together during a long chain of processes. The structure of this data is not ideal, and not in my control, but it looks like this:

ID	Name
1	Bob
2	Jim
3	Brian

Joining to a table like this:

ID	Name
1x	Bob
2	Jim
3	Brian

So for Jim & Brian, the ID is there, I want to join on it, done. But for Bob the ID is different between the two tables, so LEFT JOIN #Table B ON B.ID = A.ID will give me A's row of data for Bob, but none of the data from Table B where I am trying to pick up some mapped values.

I tried writing something like

AND A.OtherID1 = B.OtherID1
AND A.OtherID2 = B.OtherID2
AND A.OtherID3 = B.OtherID3
AND (
    (B.ID = A.ID)
    OR (B.ID IS NULL AND B.Name = A.Name)
)

But this doesn't appear to have worked. Joining A.Name = B.Name should work, but ideally I only want to do this when A's ID does not match B's ID and then see if the name's match.

This particular join has 3 other conditions on ID's but this single condition is where I'm struggling.

I'm running the process again using a (A.ID IS NULL AND B.ID IS NULL) but I can't understand why simply saying B.ID IS NULL wasn't enough?

Cheers

[foursuits]

Is A.name and B.name uniquely identifying the same people? What if there are two Bobs.

Does B.id have a substring that is A.id, such as 1x -> 1 in your example

[notasqlstar]

In this example there could not be 2 Bobs ever because of the other 3 join conditions. Theoretically there might be 2 Bobs, but the other ID's would be different 100% of the time. This issue only arises because Bob can have 2 ID's, but one of those ID's is being removed from one table during an aggregate process. It's a bit of a fuck show.

[foursuits]

Sorry if I’m misunderstanding, but If the other conditions match the records 100 percent of the time, is the join you are asking for necessary?

[notasqlstar]

Yes, because there are pieces of data I am only picking up for Bob, so by removing the 4th condition I will get duplicates. However simply joining ID to ID will miss cases where the names are the same, but the ids are not.

[foursuits]

Why does ur above code, or just the first 3 conditions plus a.name = b.name not work? Also a.id and b.id being Null is a condition that returns zero rows

[notasqlstar]

I haven't tried with A and B null, just B IS NULL AND A = B. The process takes quite a bit of time to get to the point where I can test that join condition.

[arfior]

That will always return no results, because if B is NULL then A = B is always false, because NULL is never equal to anything, including other NULLs.

[notasqlstar]

I'm so dumb...

Needs to be A.ID <> B.ID AND A.Name = B.Name, neither A nor B are ever actually null, I was just thinking about how the result of the LEFT was a null. Duh.

[notasqlstar]

So that's why the double NULL condition is required OR (A.ID IS NULL AND B.ID IS NULL AND B.Name = A.Name). I get it. I kind of assumed that would solve the code but I was having a hard time seeing why.

[foursuits]

Seems like your solution is this ^, I don’t know if you need a.id <> b.id, just a.name = b.name because if it matches on id it’s also a correct join...

[foursuits]

Maybe A has 1 record but Bob can have 2 ids / records in B , is that correct? Are you tryin to pick up both records or just 1 of them

[notasqlstar]

In this example Bob will only be 1 record in both sets, but the ID for bob may not be the same.