i have 5 tables category ,business ,metro ,user,favourites
Category
id | category_name
-------------------
1 | bar
2 | cafe
3 | lunch
4 | dinner
Business
id | business_name | zip | address |metro_id
--------------------------------------------------------------
1 | sanders | 023232 | abc1 | 1
2 | ipc | 023232 | abc2 | 1
3 | mircleFoods | 023232 | abc3 | 1
4 | salt | 023232 | abc4 | 2
Metro
id | name
-------------------
1 | boston
2 | newYork
Favourites
id | user_id | business_id | metro_id | category_id
----------------------------------------------------
1 | 1 | 1 | 3 | 1
2 | 1 | 1 | 4 | 2
3 | 1 | 1 | 3 | 1
4 | 1 | 1 | 5 | 2
User
id | firstName | LastName |userName | Password
----------------------------------------------------
1 | john | mark | jmark1 |123
2 | john | mark | jmark2 | 123
3 | john | mark | jmark3 | 123
4 | john | mark | jmark4 | 123
i have been trying to select a category with the highest count of favs in a particular city and display the business name which has its category favouried max.
i have been hoping to get result e.g
RESULT 1
{
Metro->boston
category->bar
businessName->sanders
favs->23}
RESULT 2
{
Metro->boston
category->cafe
businessName->sanders
favs->333}
i have tried many different queries but now have started trying to implement it by breaking it up here is some of the queries i have tried
1.
SELECT c.category_name,b.business_name,COUNT(f.user_id) FROM business b, category c, favourites f
INNER JOIN business ON business.id=favourites.business_id
INNER JOIN category ON category.id=favourites.category_id
GROUP BY c.category_name,b.business_name
2.
SELECT c.category_name,(SELECT COUNT(*) FROM favourites) AS fs,b.business_name FROM(favourites f,business b,category c) GROUP BY c.category_name LIMIT 0,1
3.
SELECT f.category_id, COUNT(f.user_id) AS f FROM (favourites f,metro m) GROUP BY category_id HAVING MAX(f.user_id)=(SELECT COUNT(user_id) FROM favourites)
Please kindly guide me , i am having trouble proceeding with this .Thankyou