Joing two tables in SQL with Different Primary Keys

Question

Hi,

I'm trying to join two tables, they are both string datatype but the only difference is the first table having an extension number either '001', '002', or '003', etc. I was thinking to use LIKE % but I'm not sure how. I have like thousands of rows but I'll just trimmed it down for example. Attached is the reference table.

Answer 1

Hi @Sherlan Emmanuel Burgonia
Have you validate the answers in your previous thread?
SQL: Query that finds the ID from a string
SQL Query that looks for ID number
If these two issues has been solved, please don't forget to accept the answers if they helped. So other users with similar problem could see this easier. :)

Back to the current issue,

I was thinking to use LIKE % but I'm not sure how.

I don't think LIKE is a good choice to join these two tables.
You could use string functions to ignore first three digits, as i answered on your previous thread. Like this：

select *  
from table1 t1 join table2 t2 on RIGHT(t1.ID,LEN(t1.ID)-3)=t2.ID  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 2

When they are different in values, then they are different; so what do you want to join here?

I guess you mean the first 3 digits?
Example 3124: What if they here exists?
0013124
0023124
0033124
How to join them?

Answer 3

yes I meant the first three digits.

Answer 4

You have trash data and expect pure gold as result; that's not really possible.

A close solution could be

SELECT *  
FROM table1 AS T1  
     INNER JOIN  
     table2 AS T2  
         ON T1.ID LIKE '[0-9][0-9][0-9]' + T2.ID  

= patter match on first 3 characters if they are numerics; but I still would expect data failures.