I've 4 tables namely,
nations, states, metropolitan areas, areas,
aside from nations table the relaxation three(states,metropolitan areas,areas) consists of country_id foreign key.
i needed to come back the entire quantity of count of country_id combined in three tables that i made use of jon_darstar's solution, this is actually the code i'm using.
SELECT COUNT(DISTINCT(states.id)) + COUNT(DISTINCT(cities.id)) + COUNT(DISTINCT(areas.id)) FROM states JOIN cities on cities.country_id = states.country_id JOIN areas on areas.country_id = states.country_id WHERE states.country_id IN (118);
the above mentioned code works perfectly fine although i'm not able to know the code correctly, mainly the very first line i.e
SELECT COUNT(DISTINCT(states.id)) + COUNT(DISTINCT(cities.id)) + COUNT(DISTINCT(areas.id))
Question 1 : does not that choose the primary id from the three tables states,metropolitan areas and areas making the count? i understand this isn't happening in the result i get then what is really happening here?
If however i take away the DISTINCT in the query string it shows us a different result i.e a count of 120 whereas really it ought to be 15(this is actually the count quantity of country_id in most three tables).
Question 2 : What's happening basically use DISTINCT and take away DISTINCT? is not DISTINCT designed to remove any duplicate values. where's duplication happening here?
To have an example, if inside a country A(getting primary id a.id=118),there's Condition B(getting primary id b.id),within that condition there's City C(getting primary id c.id), In city C there's Area D(getting primary id d.id),E(getting primary id e.id),F(f.id).allows visualize the query create a database table.
C St Ct Ar
(Here C=Country,St=States,Ct=Metropolitan areas,Ar=Areas)
Now imagine what goes on whenever you do rely on above table to obtain final amount of States within Country A without distinct.It makes sense 3,by doing this the amount of Metropolitan areas is 3 and areas is 3,total 9.Because without distinct you are getting duplicate values by which you are not interested.
Now,if you are using distinct count you will get correct result cause here distinct states under country A is 1,City is 1 and Areas is 3,total:5(excluding duplicate values)..
Hope this works!
Prefer to add something:Out of your database design,i can tell that you are using country id like a reference for nations from country table(to states,areas and metropolitan areas) then joining states and metropolitan areas then states and areas (by their country id)no it's creating mix join?.Better design option is at areas table keep foreign key of city,by doing this go bottom up as with city keep states as well as in states keep country.Or create a table for Areas where you stand keeping Nations,States,Metropolitan areas foreign key and areas primary key.
In the mySQL manual:
COUNT(DISTINCT expr,[expr...]) Returns a count of the amount of rows with various non-NULL expr values.
So, it returns the amount of unique States, Metropolitan areas and Areas added together.