direction on (My)SQL left/right/inner join

Question

While getting a list of restaurant I want to join rating data in this list.

For example

Table1 – restaurant
Columns: id, name, latitude, longitude

and

Table2 – ratings
Columns: id, restaurant_id, rating (value options: 1, 2, 3, 4, 5)

Constructed this:

SELECT *, SUM(ratings.rating) AS total_rating_count 
FROM restaurants 
INNER JOIN ratings 
    ON ratings.restaurant_id = restaurants.id

This only returns me 1 restaurant. I also need the amount of ratings available per restaurant in able to calculate average rating.

Can use some direction on this.

EDIT: And while we are at it, I want to join another table with comments linked to restaurant. I should dive into this at the same time: What is the difference between Left, Right, Outer and Inner Joins?

It's unclear what you're asking. Can you provide some sample rows, the desired result of your query and the actual result? — George Claghorn, Oct 10 '13 at 19:27

score 0 · Accepted Answer · answered Oct 10 '13 at 10:15

0

You need GROUP BY clause:

SELECT 
  *, AVG(ratings.rating) AS avg_rating_count 
FROM 
  restaurants 
    INNER JOIN ratings ON ratings.restaurant_id = restaurants.id 
GROUP BY 
  restaurants.id

-since you've mentioned average value, I've placed AVG() function in query. But be aware that this is invalid in normal SQL (mixing of non-group columns with group functions) - and in terms of MySQL DBMS doesn't specify which row will be returned.

answered Oct 10 '13 at 10:15

Alma Do

36,374
9
70
101

getting closer, AVG works and returns 2 records now. 2 is the amount of restaurants that actually have ratings. I need, however the complete list of restaurant even though there is no rating. – San Jay Falcon Oct 10 '13 at 10:56
@SanJayFalcon then it should be mentioned in question together with sample data & desired output. You can achieve that with `LEFT JOIN` – Alma Do Oct 10 '13 at 11:14

direction on (My)SQL left/right/inner join

1 Answers1