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

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.