Join two tables where the other table has no equivalent value in the other table using MySQL I want to join two tables where some rows does not have a value in the other table. 

This is the first table:

ID      TDNO    lotNO   area    VALUE      DATE    MEMO    
------  ------  ------  ------  ---------  ------  --------
100     1       111     100     100.00     2015    ASDASD  
101     2       222     100     100.00     2015    SDFSDF  
102     3       333     200     100.00     2015    TEST    
103     4       444     200     200.00     2015    FOO     
104     5       555     330     200.00     2015    BAR     
105     100     100     100     11,000.00  2016    100     


This is the second table:

tdno    hiid    sequence_no  
------  ------  -------------
100     qwerty              0
A) 1    qwerty              1
2       qwerty              2
3       qwerty              3
4       qwerty              4
5       qwerty              5


What I want is that I want to join the two tables but as you can see there is a row that has no value in the other table. How to join this?

This is my query so far:

SELECT 
  ht.ID,
  ht.TDNO,
  ht.lotNO,
  ht.area,
  FORMAT(ht.assess_value, 2) AS 'VALUE',
  ht.EFF AS 'DATE',
  ht.memoranda AS 'MEMO',
  ts.hiid,
  ts.sequence_no 
FROM
  history_table ht 
  RIGHT JOIN td_sequence ts 
    ON ts.`tdno` = ht.`TDNO` 
WHERE hiid = 'qwerty' 
  AND sequence_no &gt;= '0'
  ORDER BY sequence_no ASC


And the result is like this:

ID      TDNO    lotNO   area    VALUE      DATE    MEMO    hiid    sequence_no  
------  ------  ------  ------  ---------  ------  ------  ------  -------------
105     100     100     100     11,000.00  100     100     qwerty              0
(NULL)  (NULL)  (NULL)  (NULL)  (NULL)     (NULL)  (NULL)  qwerty              1
101     2       222     100     100.00     2015    SDFSDF  qwerty              2
102     3       333     200     100.00     2015    TEST    qwerty              3
103     4       444     200     200.00     2015    FOO     qwerty              4
104     5       555     330     200.00     2015    BAR     qwerty              5


What I want is like this:

ID      TDNO    lotNO   area    VALUE      DATE    MEMO    hiid    sequence_no  
------  ------  ------  ------  ---------  ------  ------  ------  -------------
105     100     100     100     11,000.00  100     100     qwerty              0
100     A) 1    111     100     100.00     2015    ASDASD  qwerty              1
101     2       222     100     100.00     2015    SDFSDF  qwerty              2
102     3       333     200     100.00     2015    TEST    qwerty              3
103     4       444     200     200.00     2015    FOO     qwerty              4
104     5       555     330     200.00     2015    BAR     qwerty              5


How to achieve this?
For example, it can look like

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