Count DISTINCT multiple mysql rows query

CrOuZ

I insert data in mysql table row using multiple method : 1#2#3 . 1 is ID of country, 2 is Id of state, 3 is ID of town . now i have this table for real estate listings. for each list(home) i have country/state/town (1#2#3). in country table i have list of country - in country table i have list of state - in country table i have list of town. i need to The number of houses in country / state / town . my mean is :

USA [ 13 ] <!-- This Is equal of alabama+alaska+arizona -->
----Alabama [8] <!-- This Is equal of Adamsville+Addison+Akron -->
-------Adamsville [2]
-------Addison[5]
-------Akron[1] 
......(list of other City)
----Alaska [ 3 ]
-------Avondale[3]
......(list of other City)
----Arizona [ 2 ]
-------College[2]
......(list of other City)

Lisintg Table :

ID -- NAME -- LOCATION -- DATEJOIN -- ACTIVE
 1 -- TEST -- 1#2#3    -- 20110101 -- 1
 2 -- TEST1 -- 1#2#3    -- 20110101 -- 1
 3 -- TEST2 -- 1#3#5    -- 20110101 -- 1
 4 -- TEST3 -- 1#7#6    -- 20110101 -- 1

Country Table :

 id       -- name  

 1        -- USA

stats Table :

id        -- countryid -- name  

 1        -- 1             -- albama
 2        -- 1             -- alaska
 3        -- 1             -- akron

town Table :

id        -- countryid -- statsid -- name  

 1        -- 1             -- 1         -- adamsville
 2        -- 1             -- 1         -- addison 
 3        -- 1             -- 1         -- akron

Thanks For Any Help.

bradgrafelman

Why not normalize the DB schema and store the data properly? In other words, get rid of the multivalued column and only store individual, atomic pieces of data in any given column.

With the example above, why not split 'LOCATION' into three columns, namely 'COUNTRY', 'STATE', and 'TOWN' ?

EDIT: Note that the reason I mention this, is that because once you normalize your DB you'll often find that it's much easier to work with in and form SQL queries that do what you need.

CrOuZ

Thanks.