question

CC-8522 avatar image
0 Votes"
CC-8522 asked MelissaMa-msft edited

TSQL inner query like join based off IN results

I have a TSQL query that has a subquery listing 2 names:
Coop
Bro


Select LastName from Managers where Type = prefix;


I need to have the outer query use this above sub-query in something like an 'IN' statement, but it's not an exact match, but rather a BEGINS WITH. Something like the following with + '%':

Select * from Employees where LastName IN (Select LastName + '%' from Managers where Type = prefix)


Desired query would bring back outer query results such as:
Cooper
Coopersmith
Coopmoth
Brown
Brownly
Bronnan


...but, this is not working. I get 0 results.

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RyanAbbey-0701 avatar image
0 Votes"
RyanAbbey-0701 answered
 select * from employees e
   inner join Managers m on e.lastname like m.lastname + '%' and m.type = prefix
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

HI @CC-8522,

Welcome to Microsoft Q&A!

Please also refer below:

 select * from employees e
 inner join Managers m on CHARINDEX(m.LastName, e.lastname) > 0  
 where m.type ='prefix'

OR

 select * from employees e
 inner join Managers m ON e.LastName LIKE CONCAT(m.LastName, '%') 
 where m.type ='prefix'

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.