Is it possible to join dynamic ? Depending on the content of a column?-CodePudding

I am using this join to retrieve rows from tblChassis where temp.ChassisNumber matches ch.ChassisNumber

While this works, it is slow.

The reason it is slow is the like '%'

select ...
from @TempTable temp
left join tblChassis ch on ch.ChassisNumber like '%'   temp.ChassisNumber   '%'

I have to use the like because sometimes I don't get the full values to search with.

How this works is, the user drops a list of chassis numbers on a form, and then the form goes to find the chassisnumbers in the table. Sometimes there will be full chassisnumbers (length = 17) but sometimes they don't have that, and they only have a part. this part could be the last xx digits, or the first xx digits, or some digits in the middle.
And off course sometimes they have a mix.

So what I did is make a temp table declare @TempTable (ChassisNumber varchar(17)) and I fill it with all the chassisnumbers that the user dropped on the form.

Then I can select from this table and join with all other tables I need.

declare @TempTable table(ChassisNumber varchar(17))

insert into @TempTable 
values ('WF0EXXTTREMG4210'),
       ('WF0JXXWPCHMB28874'),
       ('WF0JXXWPCHMB'),
       ('MB28874')
  
select ...
from @TempTable temp
left join tblChassis ch on ch.ChassisNumber like '%'   temp.ChassisNumber   '%'

So now I am trying to get the query to perform a little faster, and I wonder if there is a way to make the join dynamic, so I could use the like when the chassisnumber is not 17 digits, and use the = when the chassisnumber does has 17 digits.

When the join is ch.ChassisNumber = temp.ChassisNumber the query is so fast I cant even measure it.

Is something like that possible ?

For example this pseudocode (not working off course but to give the idea what I am looking for)

on case 
       when len(temp.ChassisNumber) = 17 
           then ch.ChassisNumber = temp.ChassisNumber
       else ch.ChassisNumber like '%'   temp.ChassisNumber   '%'
   end

EDIT

I guess I could first join on all rows from the temptable that have chassisnumbers with 17 digits, and then union that with a query for all others.

That would help but I am hoping on something better.

CodePudding user response：

I think what Stu says in there comment has some legs, however, I'd add a little more to this. Although this is a table variable, I would suggest adding a computed column to said table, and then you can do something filtering like below. This means that if all the rows in the table have a length of 17 then the bottom query should be "glossed" over as the WHERE will filter out all the rows from the variable:

DECLARE @TableVariable table (ChassisNumber varchar(17),
                              ChassisNumberLen AS LEN(ChassisNumber) PERSISTED);

INSERT INTO @TableVariable (ChassisNumber)
VALUES ('WF0EXXTTREMG4210'),
       ('WF0JXXWPCHMB28874'),
       ('WF0JXXWPCHMB'),
       ('MB28874');

SELECT *
FROM @TableVariable;

SELECT {YourColumns}
FROM @TableVariable TV
     LEFT JOIN dbo.tblChassis CH ON TV.ChassisNumber = CH.ChassisNumber
WHERE TV.ChassisNumberLen = 17
UNION ALL
SELECT {YourColumns}
FROM @TableVariable TV
     LEFT JOIN dbo.tblChassis CH ON CH.ChassisNumber LIKE '%'   TV.ChassisNumber   '%'
WHERE TV.ChassisNumberLen < 17; --I assume they can't be longer than 17