[SQL] Pull data based on state,city,county setup

Hoping someone can help with the below query where I am currently stuck.

I have a setup table (based on state, city, and county) where * in the setup represents "all" else it represents the specific value it holds. I need to extract data from the main table joining with setup table so that it only pulls population as defined in the setup table.

State City County
AZ * *
CA * Los Angeles
CA San Diego *
CA San Francisco *
CA Santa Monica *
NY * *
NY * Bronx
NY * Kings

The main table is the "person" table which has the state, city and county columns with actual person location values. I need to be able to join this with the setup table so that it only pulls the applicable rows, where * in setup table represents "all". State will always have a valid value and will never be *

Based on the above setup table, the query should pull

a) All from state NY and AZ (As it has * in both city and county)

b) from CA - all belonging to city San Diego, San Francisco and Santa Monica and all belonging to County Los Angeles.

I am getting duplicates in the current query (below). I have tried a few options like using a distinct in the final query to weed out duplicates or looking at anything else in the data that can be used instead.

SELECT
e.emplid,
e.first_name,
e.last_name ,
e.state,
e.county,
e.city

FROM
person e

JOIN setup S
ON e.state = S.State
AND (e.city = S.city OR S.city = '*')
AND (e.county = S.county OR S.county = '*')
AND sysdate between S.eff_dt and S.end_eff_dt
WHERE
sysdate between e.eff_dt and e.end_eff_dt
AND e.hr_Status <> 'T'
AND e.STAFF = 'N'
And e.union = 'NU'
AND e.country = 'USA';

Continue reading...