Combining data from 2 tables in to 1 query

Question

Hi all

Im having some problems combining data from 2 tables in to 1 query.

Now I have one table-nr1 with raw data of restaurants and in the other table-nr2 I have a number of restaurants that have been graded.

So, now I want to select all restaurants and at the same time select grades of that restaurant from table-nr2 and get the average value of those grades.

How can I do this in a single SQL query?

Answer 1

SELECT  r.*,
        COALESCE(
        (
        SELECT  AVG(grade)
        FROM    table_nr2 g
        WHERE   g.restaurant_id = r.id
        ), 0)
FROM    table-nr1 r

Answer 2

You need to perform a join. In this case an inner left join sounds fine, which is the default join. You can use USING syntax if the field that links them is the same on both sides, so you would end up with something like this:

SELECT table-nr1.*, AVG(table-nr2.score)
FROM table-nr1
JOIN table-nr2 USING (restrauntId)

Otherwise you could do something that links them using an on clause like this:

SELECT table-nr1.*, AVG(table-nr2.score)
FROM table-nr1
JOIN table-nr2 ON (table-nr1.restrauntId = table-nr2.restrauntId)

Answer 3

Assuming your restaurants have a name and id, and the your reviews have a grade

SELECT re.name, avg(ra.grade)
FROM table-nr1 re
LEFT JOIN table-nr2 ra ON re.id = ra.id
GROUP BY re.name

You need to group by all fields you want to select which are not aggregated, and left join means you will get all restaurants, irrespective of whether they have any ratings.