optimize query in db2

Question

I have two tables,

Filter, contains Country - State - City Mappings

+---------+-------+-------------+------------+
| Parent  | Child | Parent_Code | Child_Code |
+---------+-------+-------------+------------+
| Country | State | COUNT_ABC   | ST_XYZ     |
| Country | State | COUNT_POQ   | ST_TRE     |
| Country | State | COUNT_WER   | ST_QWE     |
| State   | City  | ST_XYZ      | CT_ABC     |
| State   | City  | ST_TRE      | CT_QWE     |
| State   | City  | ST_ZXC      | CT_ASD     |
+---------+-------+-------------+------------+

Names , Names of each above _Code

+---------+---------------+---------------+
| Entity  |  Entity_Code  |  Entity_Name  |
+---------+---------------+---------------+
| Country | COUNT_ABC     | United States |
| Country | COUNT_POQ     | Australia     |
| Country | COUNT_WER     | India         |
| State   | ST_XYZ        | New_York      |
| State   | ST_TRE        | Queensland    |
| City    | CT_ASD        | Banglore      |
| City    | CT_QWE        |  Sydney       |
+---------+---------------+---------------+

Now I want to write query where I have result like,

+---------------+------------+-----------+
| Australia     | Queensland | Sydney    |
| United States | New York   | Buffalo   |
| United States | New Jersey | Princeton |
| India         | Karantaka  | Banglore  |
+---------------+------------+-----------+

How to write optimize query for this thing? I have query which look like this

SELECT 
    DISTINCT
    R.Entity_Code AS COUNTRY_CODE,
    R.Entity_Name AS COUNTRY,
    SR.Entity_Code AS STATE_CODE,
    SR.Entity_Name AS STATE,
    C.Entity_Code AS CITY_CODE,
    C.Entity_Name CITY,
FROM
    NAMES C
LEFT OUTER JOIN
(
SELECT *
FROM
   FILTER F
LEFT OUTER JOIN
    NAMES C
    ON F.PARENT = C.Entity_Code
)SR ON C.Entity_Code  = SR.Child_Code
LEFT OUTER JOIN
(
 SELECT *
FROM
   FILTER F
LEFT OUTER JOIN
    NAMES C
    ON F.PARENT = C.Entity_Code
)R ON SR.Parent_COde = R.Child_Code
WHERE 
    COALESCE(R.Entity,'Country')='Country'  
    AND COALESCE(SR.Entity,'State')='State'  
   AND C.Entity = 'City'

Is thery any way to optimize it or make it short. Note: I am using DB2

Thank in advance!!

Answer 1

You can get the codes by doing a self-join on filter

select fp.parent_code as country_code, fp.child_code as state_code, fc.child_code as city_code
from filter fp join
     filter fc
     on fp.child_code = fc.parent_code;

With this as a basis, you can then join in the names:

select ec.entity_name as country, es.entity_name as state, eci.entity_name as city
from filter fp left join
     filter fc
     on fp.child_code = fc.parent_code left join
     entity ec
     on ec.entity_code= fp.parent_code left join
     entity es
     on es.entity_code = fp.child_code left join
     entity eci
     on eci.entity_code = fc.child_code;

The left join is for entities that might not have matches.