MySQL JOIN 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 | John    |
   | Howard    | Bob     |
   | Hendry    | Chris   |

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?

Answer 1

select mp.lastname, mp.change, s.position
from Staff s
inner join Max_person mp on s.lastname = mp.lastname  
    and s.name = mp.change

Answer 2

Use:

SELECT mp.*,
       s.position
  FROM MAX_PERSON mp
  JOIN STAFF s ON s.name = mp.change
              AND s.lastname = mp.lastname