How do I get data from multiple tables if the agency_code doesn't match

Question

I have a table with these fields

Employment_Form (table name)
employee_number
full_name
agency_code

The second table is

HumanResources(table name)
employee_number
full_name
agency_code

The assignment is to find the records that have a typo for agency_code on the Employment_Form table. The data in the HumanResources table is said to be 100% correct.

I am not sure how to best approach this. I am open to suggestions.

Answer 1

You can achieve that by using this select:

select ef.employee_number,ef.full_name,ef.agency_code
from Employment_Form  ef, HumanResources hr
where ef.employee_number = hr.employee_number
and ef.agency_code <> hr.agency_code;

Here you select all the columns of the Employment_Form table, fetching only those rows with matching employee_number and wrong agency_code.

If that full_name is the same on both tables, you can either use it, instead of employee_number , like this

select ef.employee_number,ef.full_name,ef.agency_code
from Employment_Form  ef, HumanResources hr
where ef.full_name = hr.full_name
and ef.agency_code <> hr.agency_code;

The output would be the same.

Hope this helps.