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
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,