mysql join返回0表示一列，其他列完好无损

Question

I wish to join multiple tables like- Categories, menus, restaurants, reviews, etc. to return the restaurants that provide the inserted food with their prices. Everything works except numberOfReviews in reviews table.

If a restaurant has no reviews then output should be 0 for numOfReviews column but other column values should be retrieved i.e. price, name, etc.

With following query I get all fields as null and count(numReviews) as 0:

 select r.id
     ,r.`Name`
     ,r.`Address`
     ,r.city
     ,r.`Rating`
     ,r.`Latitude`
     ,a.`AreaName`
     ,m.`Price`
     ,count(rv.id)
 from `categories` c, `menus` m, `restaurants` r, areas a, reviews rv
 where m.`ItemName`="tiramisu"
     and c.`restaurant_id`=r.`id`
     and m.`category_id`=c.id
     and r.`AreaId`=a.`AreaId`

and if I can't match rv.restaurant_id=r.id in where clause(obviously).

Where am I getting wrong? How do I solve this?

edited

    select  r.id, 
 r.`Name`,
 r.`Address`,
 r.city,
 r.`Rating`,
 r.`Latitude`,
 a.`AreaName`, 
 m.`Price`, 
 r.`Longitude`, 
 r.Veg_NonVeg,
 count(rv.id)
 from restaurants r LEFT JOIN `reviews` rv on rv.`restaurant_id`=r.`id`
inner join `categories` c on c.`restaurant_id` = r.id
inner join `menus` m on m.`category_id` = c.id
inner join `areas` a on a.`AreaId` = r.`AreaId`
 where m.`ItemName`="tiramisu"

Answer 1

First of all, don't use this old school syntax for the jointures.

Here is a query that may solve your problem:

SELECT R.id
    ,R.Name
    ,R.Address
    ,R.city
    ,R.Rating
    ,R.Latitude
    ,R.Longitude
    ,A.AreaName
    ,M.Price
    ,R.Veg_NonVeg
    ,COUNT(RV.id) AS numOfReviews
FROM restaurants R
INNER JOIN categories C ON C.restaurant_id = R.id
INNER JOIN menus M ON M.category_id = C.id
INNER JOIN areas A ON A.AreaId = R.AreaId
LEFT JOIN reviews RV ON RV.restaurant_id = R.id
WHERE M.ItemName = 'tiramisu'
GROUP BY R.id, R.Name, R.Address, R.city, R.Rating, R.Latitude, R.Longitude, A.AreaName, M.Price, R.Veg_NonVeg

I used explicit INNER JOIN syntax instead of your old school syntax and I modified the jointure with table reviews in order to get the expected result. The GROUP BY clause is required to use the aggregate function COUNT, every rows will be grouped by the enumerated columns (every column except the one used by the function).

Here is another solution that simplify the GROUP BY clause and allow the modification of SELECT statement without having to worry about the fact that every columns need to be part of the GROUP BY clause:

SELECT R.id
    ,R.Name
    ,R.Address
    ,R.city
    ,R.Rating
    ,R.Latitude
    ,R.Longitude
    ,A.AreaName
    ,M.Price
    ,R.Veg_NonVeg
    ,NR.numOfReviews
FROM restaurants R
INNER JOIN (SELECT R2.id
                ,COUNT(RV.id) AS numOfReviews
            FROM restaurants R2
            LEFT OUTER JOIN reviews RV ON RV.restaurant_id = R2.id
            GROUP BY R2.id) NR ON NR.id = R.id
INNER JOIN categories C ON C.restaurant_id = R.id
INNER JOIN menus M ON M.category_id = C.id
INNER JOIN areas A ON A.AreaId = R.AreaId
WHERE M.ItemName = 'tiramisu'

As you can see here I added a new jointure on a simple subquery that does the aggregation job in order to provide me the expected number of reviews for each restaurant.

Hope this will help you.