Query inside query

kikki

Hi folks,
The query I built doesn't work properly... I need help creating a query.. but I've never done such complex query before, Please I need your help...

//Tables
Cities(id, city)
UserGroups(user, city, country)
Users(name, country)

I'm trying to list all the cities where users are from, and then count the users from the same city and display as follows:

New York(12)
Madrid(17)
London(21)
...

TIA

reddrum

If the there is a relationship from UserGroup.user and User.name then you could do smoething like this and then use mysql_num_rows() to get your record count.

$query = "SELECT user, city, country, name, country
            FROM UserGroups INNER JOIN Users ON UserGroups.user = Users.name
            WHERE UserGroups.city = 'New York'";

YAOMK

You need to reconsider your tables, how do you associate users with cities? if you plan on doing it by "id", you must have that field associated with the users table, the same applies to your userGroups table, unless you have an additional table which wasn't stated. Once you have your table relationships established. You could create a temporary table and store users and cities there and add up instances of cities etc, or you could run joint queries, store results in php and present data from there.

EDIT: reddrum, I didn't realize your post, that is where I was going. Thanks.

kikki

Thanks for your replies reddrum & YAOMK,
my tables have associations with one and other..

For example..

Cities field has 2 fields(id, cities), when users register they select a city, and the city is saved in the users table in city field.. which means city has association with the table called: Cities

UserGroups table has a field called 'country' and Users table has a field called country as well, which means Users and UserGroups are related in that field.

I'm trying to list all the cities and number of users who are from that city...
Like I've mentioned before..

New York(12)
Madrid(17)
London(21)
...

Previous query which was posted by reddrum selects 5 fields from UserGroups, uses inner join to see if usergroup has a field called user and checks with user field... but usergroup has ID for users.. and Users table has a groupID field filled with the group ID...

Maybe a proper query might work... but this is just an idea of what I'm seeking..

$query = "SELECT city FROM Cities, 
count(*) AS CityCount FROM Cities
WHERE Cities.city IN ( SELECT city FROM Users, Cities WHERE Cities.city = Users.city )  AND ( SELECT country FROM UserGroups WHERE UserGroups.country = Users.country )
GROUP BY `city` 
ORDER BY `city` ASC";

YAOMK

Kikki I see the relationship you mentioned. Let me ask you this, how do you know in your database, what user belongs to what city? how does one establish that one relationship? In other words I don't see a common field between tables "users" and table "cities". Perhaps the association you intended takes place in table userGroups and the "user" field is meant to be the same as the "name" field?

If I were you I'd focus on the design of the database before I'd proceed to write queries, temporary tables or stored procedures. I'm a fan of the latter so my next step would be to use a user defined function and return the value you desire. But I could be wrong on what it is you are trying to achieve.

kikki

YAOMK wrote:
Kikki I see the relationship you mentioned. Let me ask you this, how do you know in your database, what user belongs to what city? how does one establish that one relationship? In other words I don't see a common field between tables "users" and table "cities". Perhaps the association you intended takes place in table userGroups and the "user" field is meant to be the same as the "name" field?

If I were you I'd focus on the design of the database before I'd proceed to write queries, temporary tables or stored procedures. I'm a fan of the latter so my next step would be to use a user defined function and return the value you desire. But I could be wrong on what it is you are trying to achieve.

Thanks for the reply YAOMK, I see what you mean.. It really doesn't have to contain only one query to do this trick.. before all these, I've done it with 2 queries... but while listing the cities I've executed the 2nd query to do the counting... just like you said.. doing a function and drawing the value to execute a query... The problem was it didn't work well, as a result it has showed more/less members from a city... Thats why I thought to ask from experts IF it's possible to do this using some other method....

Cities have some sort of relationship with UserGroup.city field, and Users table have some sort of relationship with UsersGroup(country/id) and UserGroup has some sort of relationship with Users(country,GroupID). I was just wondering if it's possible to draw results with these relationship..

If I can't get any help to solve this issue, I will have to create another field(city) in Users table and write a new query (Then I guess have to do a lot of work manually updating the city field for all users).