Toad World® Forums

Help w. Group By Function/Query Builder

I am trying to run a query that finds name mismatches between systems. Basically, I'm saying Select EMP_ID, System1_Name, System2_name, where System 1_name <>System2_name.

The problem that I am having is that the table for system1 includes multiple rows for one person (in cases of name changes) with an effective date. The table for system2 only includes the current name.

For example:
Table 1:
EMPLID Name EffDT
1 Smith 1/1/2012
1 Jones 1/1/2018
2 Miller 3/1/2016
3 Teams 1/4/2019
3 Frank 3/5/2020
3 Lee 1/1/2021

Table 2:
EMPL_ID Name
1 Jones
2 Miller
3 Lee

The results I would expect to see from the query would be:

EMPL_ID Table1_Name Table 2 Name
1 Jones Jones
2 Miller Miller
3 Lee Frank

New to data point AND SQL, so any help would be appreciated!