quote:

---

Originally posted by jprogrammer

Please check below tables


I am trying to get number of states and districts using this query
Select
c.chcountrycode,
count(distinct s.chstatecode) as states,
count(d.chdistrictcode) as districts
From
Country C
inner join Countrystatelist CS on c.chCountrycode = c.chCountrycode
inner join table_statelist sl on cs.statelistid = sl.statelistid
inner join table_state s on s.chstatecode = sl.chstatecode
inner join statedistrictlist sdl on sdl.chstatecode = s.chstatecode
inner join table_districtlist dl on dl.districtlistid = sdl.districtlistid
inner join table_district d on d.chdistrictcode = dl.chdistrictcode
Group by c.chcountrycode

But i am getting the result set like
this

countrycode-states - districts
US 3 3

Actually i got 3 districts and 2 states in the database.
Please help me to write this query.

Thanks in advance.

---