SQL Left Join with where clause returning null values

Question

I have a problem with a Join that I can't seem to fix, this is the cut down version of code

SELECT top 5
a.Name00,
b.MIDNumber

FROM
[CM_UOC].[dbo].[Computer_System_DATA] AS a

LEFT OUTER JOIN
[UoC_Inventory].[dbo].[Inv_DATA] AS b on b.MIDNumber like '%'+a.Name00+'%'

Basically the ID's are the same but some ID's in a.Name00 have leading or trailing characters, such as "AVMID.." any matches based on these entries are found and joined but any columns selected from table 'b' are returned as NULL. The join is fine if there are no leading or trailing characters.

What I'm getting is

Name00      MIDNumber
AVMID014253 NULL
AVMID10059  NULL
AVMID10061  NULL
AVMID10063  NULL
AVMID10064  NULL

What I need is

Name00      MIDNumber
AVMID014253 MID14253
AVMID10059  MID10059
AVMID10061  MID10061
AVMID10063  MID10063
AVMID10064  MID10064

I understand that a LEFT JOIN will return null values if there isn't a match but how can I join and get the output I'm looking for?

EDIT

Fixed the leading characters with this

WITH A AS
(
SELECT 'M'+right(Name00, len(Name00) - charindex('M', Name00)) as 'A_MID'
FROM [CM_UOC].[dbo].[Computer_System_DATA]
),
B AS
(
SELECT MIDNumber AS 'B_MID'
FROM [UoC_Inventory].[dbo].[Inv_DATA]
)
SELECT * FROM A LEFT JOIN B ON (B.B_MID = A.A_MID)

WHERE B.B_MID IS NOT NULL
ORDER BY A_MID DESC

Still having problems trimming any leading characters

You are `left join`ing one table. The `where` clause does not mention that table, so the `where` clause has no affect on the `left join`. — Gordon Linoff, Jul 30 '18 at 10:37
The WHERE clause mentions a.Name00 so wouldn't that cause it to revert to an INNER JOIN which would explain why I'm getting null values back from table h? — C Mills, Jul 30 '18 at 10:49
No it won't do any such thing. The WHERE operates (conceptually speaking) on the result of the FROM and does not "affect" or "alter" it in any way. If your FROM says LEFT JOIN then what will happen is a LEFT JOIN, period. — Erwin Smout, Jul 30 '18 at 11:00
The problem is, it's treating it like a left join. Running the query as it is leaves any columns from table h as NULL if there is a mismatch on the join. If I run the query without the final and statement, everything returns fine. — C Mills, Jul 30 '18 at 12:49
Possible duplicate of [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — philipxy, Jul 30 '18 at 16:53
Hi. Learn what left join on returns: inner join on rows plus unmatched left table rows extended by nulls. Always know what inner join you want as part of a left join. PS "Basically" & "The join is fine" & "revert to an INNER JOIN" & "treating it like a left join" & "everything returns fine" are not clear. Please: Use enough words, phrases & sentences to clearly say what you mean. Clarify via edits not comments. Read & act on [mcve]. — philipxy, Jul 30 '18 at 17:07
You've edited to give an example of `AVMID014253` being matched to `MID14253`, but that extra zero in the middle means that your wildcards on the ends is not going to be enough. Without knowing all the alternatives of your data, I cannot possibly offer an answer. — Richardissimo, Jul 31 '18 at 08:46
@Richardissimo Okay fair enough I'll look into that but the rest of the columns still return null with just 'AV' as the leading character. — C Mills, Jul 31 '18 at 08:56

score 1 · Answer 1 · answered Jul 31 '18 at 09:30

You can use CHARINDEX function in this case :

SELECT top 5 a.Name00,
             b.MIDNumber
FROM [CM_UOC].[dbo].[Computer_System_DATA] AS a
   LEFT OUTER JOIN [UoC_Inventory].[dbo].[Inv_DATA] AS b 
                on CHARINDEX(b.MIDNumber,a.Name00) > 0

SQL Left Join with where clause returning null values

1 Answers1