Query Problem

Tamer_Ahmed

Centurion
Joined
Dec 20, 2003
Location
Egypt
hi i have a problem in my Query iu don't know how to do it
i have Table called Countries_cities with self join with him self contain county names and cities there relation between country and cities as we know
i have another table called employers i want to select count(employer_email)
group by country only i don't want to get city name in my Result can anyone help
 

JABE

Junior Contributor
Joined
Jun 24, 2003
Location
Philippines
SQL problems are best understood when you include DDL (CREATE TABLE ...) statements, some sample data and expected output given the sample data. I suggest that you include those things in your post.
 

Joe Mamma

Senior Contributor
Joined
Mar 1, 2004
Location
Washington DC
Hi tamer. . .


A proper model would be two different tables for the city/country

something along the lines of -

Bold is primary Key
Underline is a unique index
(Fld)->TableName) fld is foreign key reference to TableName
Visual Basic:
_________________________________________________________________________________________________________________________________________________________________
Country (countryID, countryName, otherCountryData)
City (cityID, countryID, cityName, otherCityData) (CountryID) ->Country
Person(taxID, firstName, middleName, lastName, primaryEmail, otherPersonData, cityID) (cityID)->City
Employer(employerID, employerName, cityID, employerEmail) (cityID)->city
Dept(deptID , employerID, deptName, managerID) (managerID) -> Employee, (employerID)->Employer
Job(jobCode, title, deptID) (deptID)->Dept
Employee(taxID, jobCode, managerID, otherEmployeeData, employeeEmail)(taxID) ->Person,(managerID)->Employee, (jobCode) -> Job

your select is (I believe as I am doing this off the top of my head)

select c.countryName, count(e.employerEmail) from
Country c inner join (City ct inner join Employer e on ct.cityId = e.cityId)
group by c.CountryID
order by c.countryName

No CityId, CountryId, EmployerId, TaxId, etc no. . . are not the primary keys. nor should they be!!!
 
Top Bottom