getting NULL value for string 'marketing' and 'makeing' as soundex drops vowels only as both have same soundex string value

Question

Write a query to return the list of employee IDs with incorrectly spelled departments, from the Emp Master table. correct data is in Dept_Master with no foreign key.

SELECT * FROM Emp_Master as orig
LEFT OUTER JOIN Dept_Master as correct
ON SOUNDEX(orig.Department) = SOUNDEX(correct.Department_Name)
WHERE orig.Department  NOT IN (SELECT Department_Name FROM Dept_Master)

tried this but getting NULL value for string 'marketing' and 'makeing' as soundex drops vowels only as both have same soundex string value. how can I solve it?

Answer 1

select distinct orig.Emp_ID,(orig.department) from Emp_Master as orig,Dept_Master as correct 
where orig.Department not in (select Department_Name from Dept_Master);