Calculate Distinct count when joining two tables

Question

id1     id2    year State Gender    
====  ====== ====== ===== =======    
1       A      2008    ca      M    
1       B      2008    ca      M    
3       A      2009    ny      F   
3       A      2008    ny      F     
4       A      2009    tx      F

select
    state, gender, [year],
    count (distinct(cast(id1 as varchar(10)) + id2))
from
    tabl1
group by state, gender, [year]

I could find the account by calling. Now I need to find a clear account for the city. as in CA - 3 cities .. sfo, la, sanjose. I have a lookup table in which I can find the state and city.

    table2 -  city     
    ====           
   cityid  name
     ==== ====
     1   sfo                
     2   la                    
     3   sanjose 

    table 3 - state
     ====   
stateid   name
   ==== ====
    1    CA
    2    Az

table 4 lookup state city
 ====   
pk_cityId pk_state_id
1             1
2             1

select state,city,gender, [year],
        count (distinct(cast(id1 as varchar(10)) + id2))
    from
        tabl1 p
    group by state, gender, [year],city

this query to find the name of the city and state.

select c.city,s.state from city_state sc
inner join (select * from state)s on sc.state_id = s.state_id
inner join (select * from city)c on sc.city_id = c.city_id

I made it look like this query using a lookup table, but the problem is that I get a clear count in all states, and the same counter is repeated for every city in the state.

ex: for count for ca: 10, then the graph for cities should be like La - 5, sanjose - 4, sfo-1.

but with my request I get as sfo - 10, la-10, sanjose-10 .. I could not find an account for a lower level. Any help would be appreciated.

UPDATE: I updated the query and lookup tables.

Answer 1

, - , :

;WITH DistinctState AS
(
    SELECT  DISTINCT
            id1
        ,   id2
        ,   [year]
        ,   [State]
        ,   Gender
    FROM    tab1
)
SELECT  s.state
,       c.city
,       gender 
,       [year]
,       count(*) 

FROM     DistinctState s
    INNER JOIN
        tab2 c
    ON  s.id1   = c.id1
 AND    s.id2   = c.id2

GROUP   BY 
        s.state
    ,   c.city
    ,   gender
    ,   [year]

Answer 2

, , :

, (1) (2) . , ; : 10.

DDL ? , (city_id?), .