MySQL - Can I combine these 2 SQL statements? Combine JOIN and AVG?

Question

Can I combine these 2 SQL statements? Currently running 2 queries. Trying to tighten up a bit.

First one:

SELECT * FROM (`cars`) 
JOIN `brands` ON `brands`.`br_id` = `cars`.`brand_id`
WHERE `cars`.`id` = '185707'

Second one:

SELECT ROUND(AVG(rating)) as avg_rating
FROM car_ratings WHERE car_id = 185707

Answer 1

select *

, (select round(avg(rating)) from car_ratings 
where car_id = cars.id) as avg_rating

from cars join brands on brands.br_id = cars.brand_id
where cars.id = 185707

But whether or not that represents an improvement is another question, best answered by seeing what query plan is being used.

Answer 2

You can do this using group by:

select cars.*,
       brands.*, 
       round(avg(car_ratings.rating)) as avg_rating
from   (cars
    inner join brands on brands.br_id = cars.brand_id)
    left join car_ratings on car_ratings.car_id = cars.id
where  cars.id = 185707
group by cars.id

Note that this is a MySQL extension to standard SQL; in standard SQL you would need to list all of the selected fields in the group by clause.