errors solving SQL challenge: crew members who have a different role in new_database than they do in old_database AND who are still employed

Question

I am trying to solve a challenge "Identify" in Lost at SQL:

"start_dates". This dataset provides you with valuable information about the employees, including their ID number, date of birth (DOB), employment start date, and their current employment status. It seems like a promising source of information that can help you piece together the puzzle.

"old_database". This database contains outdated information about the employees and their previous occupations. While it may not be as up-to-date as you would like, it could still provide some valuable insights into the history of the employees and their past roles.

"new_database". This dataset appears to be more current, containing updated information about the employees and their current employment status. It seems like a goldmine of up-to-date data that could potentially fill in the gaps left by the old_database.

START_DATES -> id_number , date_of_birth , employment_start_date , employed_or_departed

NEW_DATABASE -> id_number , full_name , date_of_birth , occupation

OLD_DATABASE -> first_name , last_name , id_number , date_of_birth , occupation

Get a table with the columns Full_Name, employment_start_date, latest_occupation, and previous_occupation. It should be filtered to just crew members who have a different role in new_database than they do in old_database AND who are still employed.

I tried

SELECT full_name AS Full_Name, a.occupation AS latest_occupation, b.occupation AS previous_occupation, employment_start_date
FROM new_database 
FULL OUTER JOIN old_database b ON b.occupation <> a.occupation
FULL OUTTER start_dates c ON a.id_number = c.id_number
WHERE employed_or_departed = 'Employed'

This gives me a list of the same names repeating many time with all the different roles. But it is not the right answer.

I also tried

SELECT full_name AS Full_Name, a.occupation AS latest_occupation, b.occupation AS previous_occupation, employment_start_date
FROM new_database 
FULL OUTER JOIN old_database b ON a.id_number = b.id_number
FULL OUTTER start_dates c ON a.id_number = c.id_number
WHERE employed_or_departed = 'Employed' AND a.occupation<>b.occupation

What gets the right answer?

What am I missing?

Answer 1

I don't know why you use FULL OUTER JOIN in this case when you can simply use INNER JOIN here. Here is the query:

SELECT
    nd.full_name AS Full_Name,
    sd.employment_start_date,
    nd.occupation AS latest_occupation,
    od.occupation AS previous_occupation
FROM
    start_dates sd
INNER JOIN
    new_database nd ON sd.id_number = nd.id_number
INNER JOIN
    old_database od ON nd.id_number = od.id_number
WHERE
    sd.employed_or_departed = 'Employed'
    AND nd.occupation <> od.occupation;

You can check the sample fiddle here