Join three tables together to get one value

Question

I'm new to Postgresql and I have three tables:

=# SELECT * FROM employee_names;
 employee_id | employee_name
-------------+---------------
           1 | Johannes
           2 | Günter
           3 | Elsabeth

=# SELECT * FROM projects;
 employee_id | last_project
-------------+--------------
           1 | 5
           2 | 15
           3 | 8

=# SELECT * FROM last_project_systems_used;
 employee_id | last_project | systems_used
-------------+--------------+--------------
           1 |            5 |           11
           1 |            5 |           18
           2 |           15 |            4
           3 |            8 |           10
           3 |            8 |            7
           3 |            8 |            2

• employee_id in employee_names is pri key and serial.
• employee_name is unique. Yes I know in typical use cases a name should NOT be given unique treatment as people can easily have identical names but in my example it's unique.
• employee_id in projects is pri key.
• last_project is not null integer and the number can be repeated in both tables (the project number of the last project they worked on)
• systems_used is unique (the system number they worked on, will always be unique values)

To summarize:

• Johannes has an employee_id of 1, his last project was #5, and he used both systems 11 and 18 in his project.
• Günter has an employee_id of 2, his last project was #15, and he used both system 4 in his project.
• Elsabeth has an employee_id of 3, her last project was #8, and she used systems 10, 7 and 2 in her project.

I need to run an insert query somewhat like the following:

    INSERT INTO last_project_systems_used 
    VALUES ((SELECT employee_id FROM employee_names 
    WHERE employee_name = 'Günter'), 
    (SELECT last_project FROM projects 
    WHERE (SELECT employee_id FROM employee_name 
    WHERE employee_name = 'Günter')), 9);

This would check what the employee_id is for Günter (2), find what his last project number was (15) and add system number 9 into the table.

That update would look like:

 employee_id | last_project | systems_used
-------------+--------------+--------------
           1 |            5 |           11
           1 |            5 |           18
           2 |           15 |            4
           3 |            8 |           10
           3 |            8 |            7
           3 |            8 |            2
           2 |           15 |            9

Though I am having trouble with the part where I get the last project number from the employee with the name Günter, since only his id is listed in the projects table and not his name.

Any help would be appreciated.

Answer 1

Use INSERT . . . SELECT:

INSERT INTO last_project_systems_used (employee_id, last_project, systems_used)
    SELECT en.employee_id, p.last_project, 9
    FROM projects p JOIN
         employee_names en
         ON p.employee_id = en.employee_id
    WHERE en.employee_name = 'Günter';

Answer 2

With enough trial and error I found this this appears to do the trick, if there are better solutions to this or if my SQL sounds bad please let me know.

INSERT INTO last_project_systems_used VALUES
((SELECT employee_id FROM employee_names
WHERE employee_name = 'Günter'),
(SELECT last_project FROM projects
WHERE employee_id = (SELECT employee_id FROM employee_names
WHERE employee_name = 'Günter')), 9);