counting top cities from a table

[Makavely]

Hello everyone,

I have some problems in counting the top cities from a table like:

id,job,cities (where cities are string)

example of rows:

id| job | cities
1) 1 | job1 | New York,Los Angeles,California
2) 2 | job2 | Los Angeles,California
3) 3 | job3 | Washington DC,Alabama,Alaska
4) 4 | job4 | Alaska
........

I also have a table with the cities names (id, city) witch i will use to count the number of each city occurrence.


All i can manage to do is:

SELECT COUNT(id) as number,cities
FROM content
WHERE cities IN (SELECT city FROM cities_names)
GROUP BY cities ORDER BY number DESC

As my column cities contain more cities because it's a string, it will count only the rows that have only one city in the "cities" string column. How can i count them correctly?


Thanks in advance,
Cristian

[Anpher]

Why don't you cache count of jobs in your cities table (id, city, jobs) ?
Also I would suggest to use relation table..

[Kailash Badu]

You shouldn't lump together cities into a single string and store it in a single column. Store them in separate rows if you want to issue the query you mentioned. Problem like this is exactly why you should always normalize your tables.

[Makavely]

i agree with you guys but i want to store data in a single table because it will be faster when you have for example 500.000 results. Inner, Outer joins from my experience have a lot of problems when it comes to large databases.

So, how can this sql be done without normalization?

[r937]

i agree with you guys but i want to store data in a single table because it will be faster when you have for example 500.000 results.

this is a misconception

Inner, Outer joins from my experience have a lot of problems when it comes to large databases.

this is false

[PHPycho]

I would like to request to read this book if possible:
http://oreilly.com/catalog/9780596527990

It contains lots of hacks/tweaks.
Additionally it also contains the Date Hacks section by our db expert 'Rudy'