mysql - SQL JOIN OPENQUERY Assistance

Question

Looking for advise on the best way to approach the following

TBL1 (Local SQL DB) where ID = varchar

TBL2 (Remote MYSQL DB) where ID = INT

WITH TBL1 as (
SELECT 
     [Results]  as 'ID'
 FROM [DB].[results]
),

TBL2 as (


select * from openquery(LINKEDSERVER,'select  ID, Name from DB')

) 

Select  
   TBL1.[ID],

   TBL2.[NAME] 
FROM [DB]
left outer JOIN TBL1 ON TBL1.ID = TBL2.ID

Tried a couple different methods like CAST/CONVERT, but ending up with either an error like "Conversion failed when converting the varchar value '12345 ' to data type int." or getting all NULLS for TBL2.

Attempted the following:

WITH TBL1 as 
( 
SELECT CAST(ISNULL([Results],'0') AS INT) as 'ID' 
FROM [DB].[results] 
), 
TBL2 as ( select * from openquery(LINKEDSERVER,'select ID, Name from DB') 
) 

Select TBL1.[ID], TBL2.[NAME] 
FROM TBL1 Left Outer join TB2 ON TBL1.ID = TBL2.ID

This results in "Conversion failed when converting the varchar value '12345 ' to data type int."

Also Attempted:

WITH TBL1 as ( SELECT [Results] as 'ID' FROM [DB].[results] 
), 

TBL2 as ( select * from openquery(LINKEDSERVER,'select CONVERT(ID, CHAR) AS ID, Name from DB') 
) 

Select TBL1.[ID], TBL2.[NAME] 
FROM TBL1 Left Outer join TB2 ON TBL1.ID = TBL2.ID 

This results in the TBL1.ID values being displayed, but all the TBL2.Name values are NULL

question from:https://stackoverflow.com/questions/65948755/sql-join-openquery-assistance

Answer 1

Your syntax doesn't look right. Where does the DB C come from? Isn't DB the schema name?

WITH TBL1 as (
SELECT 
     [Results]  as ID
 FROM [DB].[results]
),
TBL2 as (
    select * from openquery(LINKEDSERVER,'select  ID, Name from DB')
) 

Select  
   TBL1.[ID],
   TBL2.[NAME] 
FROM TBL2 left outer JOIN TBL1 ON try_cast(TBL1.ID as int) = TBL2.ID;