How to apply different conditions to join, bases a columns value in DB2

Question

Am having a data as below

Table emp

Cty	name	flag
New York	aa	na
Gua	bb	city

Table city

Id	city	name
1	new york	aa
2	ohio	bb

I want to apply join based on flag columns value in single query.

Such as when flag is NA

flag='na' then emp.name=city.name

flag='City' then emp.name=city.name and emp.cty=city.city

Answer 1

Try this:

SELECT *
FROM EMP
JOIN CITY ON emp.name=city.name
and  (emp.flag='na' or emp.flag='city' and emp.cty=city.city)