Tricky data retrieval - TOP5 locations

[KateOnAcid]

Hey, guys!

 

I've been at this for days and I just can't seem to find a solution. I've googled every possible keyword combination to no avail..

The closest I've found to my needed solutions is this:

SELECT p.name,count(t.id) as transactionCount
FROM transactions t
LEFT JOIN partners p ON p.id=t.partner_id
GROUP BY p.name
ORDER BY 2 DESC 
LIMIT 1

 

But this query assumes that the string to be counted is not part of anything.

 

 

I have two tables:

cities

IDname

300014Paris

300015London

300016Tokyo

 

user_info

IDlocations

100014300014,300015

100015300015,300016

100016300014,300015,300016

 

What i want is to have a TOP5 locations list, like this:

London (3)

Paris (2)

Tokyo (2)

...

 

Some of this may sound trivial to you, but it's been bugging the hell out of me.

Can you guys give me a few pointers in the right direction?

Maybe there's a better way to do this?

 

KOA

[darkfreaks]

might want to take a look at INNER JOIN it  will return one match if there is a match in both tables.

[KateOnAcid]

might want to take a look at INNER JOIN it  will return one match if there is a match in both tables.

 

This advice was spot on! Thank you!

 

My current statement looks like this:

SELECT cities.id, cities.name, COUNT(user_info.location) AS cnt
FROM cities
INNER JOIN user_info
ON user_info.location LIKE concat( '%',cities.id, '%') 
WHERE user_info.location!=''
GROUP BY cities.id
ORDER BY cnt DESC
LIMIT 0, 5