how to do a sql inner join between 2 tables when using a partial key?

Can anyone give me an example of a sql inner join between 2 tables when using a partial key?

For example:

======
Table1
======

StudentID: STUDENT00001, STUDENT00002, STUDENT00999


===开发者_StackOverflow===
Table2
======

ID: 00001, 00002, 00999

My code so far:

SELECT *
FROM Table1 
  INNER JOIN Table2
    ON Table1.StudentID = ***how do i add a prefix here?*** Table2.ID

THANKS!

Use the CONCAT() function in MySQL.

SELECT *
FROM Table1 
INNER JOIN Table2
ON Table1.StudentID = CONCAT('STUDENT',Table2.ID)

Maybe like this:

SELECT *
FROM Table1 
  INNER JOIN Table2
    ON Table1.StudentID = CONCAT('STUDENT', RIGHT(100000 + Table2.ID, 5))

CONCAT(str1, str2, …) returns a string that is the result of concatenation of str1, str2 etc.

RIGHT(str, len) returns str's rightmost len characters. str in this case is represented by the result of 100000 + Table2.ID, implicitly converted to a string.

I am assuming here that Table2.ID is an integer, so the entire RIGHT() expression is intended to turn the integer into a string where the integer value is padded with zeros. If Table2.ID is a string and is already padded with zeros, you don't need RIGHT, of course. The join condition would then look just like this:

… ON Table1.StudentID = CONCAT('STUDENT', Table2.ID)

Should simply be able to concat

SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.StudentID = 'STUDENT'+CAST(t2.ID AS NVARCHAR)