1

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 >= '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?

  • Explain, why the record with tdno='A) 1' was joined with the record with TDNO='1'. Even better to formulate a criterion which allows to find those "not equal, but matched" record pairs... then convert it to the statement and use it in JOIN condition. – Akina Jun 14 '18 at 4:29
  • the tdno = A) 1 was entered by the user and i want to connect it to TDNO=1 which is already in the database – Rak Jun 14 '18 at 5:05
  • You say what, and don't say why. Why with TDNO=1 and not with TDNO=11, for example... In practice - autocorrection of user's misprintings gives poor quality results needed in manual checking. – Akina Jun 14 '18 at 6:36
0

For example, it can look like

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.