before thinking about what kind of joins to name_src you want, please explain why you want to join to name_src at all... because your query isn't using it in any way

also, have a look at the join conditions for the join to nick_test -- you seem to want only those results where master_test.fn is equal to ~both~ nick_test.name and nick_test.nickname, which might be perfectly valid but...

I need to match the fn and ln in NAME_SRC to the fn and ln in MASTER_TEST so I can target the complete_addr in MASTER_TEST and its associated fn and ln. The nick_test table exists to target related firstnames (jim/james, james/jim, etc).