MySQL JOIN table query help

Question

Hello, I hate asking for code but I just can't seem to do the below -

Staff

   | lastname  - name    - position |
   | Henderson | John    |  A       |
   | Howard    | Bob     |  B       |
   | Hendry    | Chris   |  B       |

Max_person

   | lastname  - change  |
   | Henderson | 0.9     |
   | Howard    | 0.2     |
   | Hendry    | 0.3     |

I have been trying to JOIN these 2 tables - adding the "POSITION" column of staff to the end of max_person.

What is the query to use here?

PS. Sorry about my previous question - I got the tables wrong and am now totally lost

Answer 1

Use:

   SELECT mp.lastname,
          mp.change,
          s.position
     FROM MAX_PERSON mp
LEFT JOIN STAFF s ON s.lastname = mp.lastname

If there's no matching lastname value between the two tables, the position value will be NULL. Change "LEFT JOIN" to "JOIN" if you only want to see the records that match...

Design tables better

This is risky to join on the last name, because multiple people can have the same last name. First name can also be duplicated yet represent a different person...