Search code examples
sql-serverleft-joinsql-like

SQL Server Left Join LIKE unexpected result

I have a LEFT JOIN in my query that works for the majority of the query. I have come across a record which it is not working on and cannot figure out why.

I am doing:

SELECT t1.StrSerialNumber, t2.serialNumber

FROM table1 AS T1 

LEFT JOIN
table2 AS t2 ON t1.StrSerialNumber LIKE '%' + t2.serialNumber + '%'

...t1.StrSerialNumber could have an extra character on either end. Otherwise, these 2 columns should match.

This is working as expected (or hoped) except for 1 row, which makes me think there could be more.

When I run:

select * from t2 where serialNumber like '%' + 'number from t1.StrSerialNumber' + '%'

...as a separate query, it DOES return what I am looking for! I can see when querying t2 directly for value LIKE, it is there. It just does not work as part of the JOIN.

All columns in t1 are not null, so it is not like there is some other missing info.

I even UPDATED t2.serialNumber, not to match t1.StrSerialNumber, but to make sure there are not spaces in the field, etc.

Any 'gotcha's' for this scenario??

Thank you!!

EDIT

Here is an example of where it fails.

The green box at the bottom shows the expected results from the JOIN statement, within the green box in the query itself.

The red box in the query results shows the row where I expect a 'like' match.

The orange box in the workspace is the query that returns the serialNumber that I expect to be in row 42, googleSN column.

The yellow line just shows where I think the serialNumber should be going in the JOIN.

Obviously I do NOT expect a 'like' match for all of the '0' values in invSN.

I hope this makes sense!

enter image description here

Solution

  • The second query does not return a subset of the first (abusing the word subset). The LEFT JOIN query wants the t1.SN to be contained in a t2.SN value. The second query wants the t2.SN to be contained in a t1.SN value. So I guess you might have a problem with your requirements.

    If the phenomenon you describe is the only problem you have, a simple OR will solve the problem:

    SELECT t1.StrSerialNumber, t2.serialNumber

FROM table1 AS T1 

LEFT JOIN
table2 AS t2 ON t1.StrSerialNumber LIKE '%' + t2.serialNumber + '%'
    OR t2.serialNumber LIKE '%' + t1.StrSerialNumber + '%'