Join two tables where the other table has no equivalent value in the other table using MySQL
For example, it can look like

FROM table1
JOIN table2 ON table1.TDNO = SUBSTRING_INDEX(table2.tdno, ' ', -1)