SQL - 从另一个表中获取AVERAGE值

My question sounds really easy, but I'm stuck.

    Sample Data:

    Listing:

    id title                State 
    1  Hotel with nice view Arizona
    2  Hotel to stay        Arizona

    Review:

    id listing_id rating  mail_approved
    1  1          4(stars)  1
    2  1          4(stars)  0
    3  1          3(stars)  1
    4  2          5(stars)  1

So now I get the AVG value of the listings, but I want to get only the value of each listing when the review is mail_approved = 1. But when there is none review or no review with mail_approved = 1 it should give me the listing back just with 0.0 review points. So I would like to get all listing back if they have a review just calculate the AVG of those reviews with mail_approved = 1

How can I do this? Do I have to rewrite the whole query?

Here is my query:

   SELECT
     ls.id,
     title,
     state,
     ROUND(AVG(rating),2) avg_rating
   FROM listing ls
   JOIN review rv
   ON ls.id = rv.listing_id
     WHERE ls.state = '$get_state'
   GROUP BY ls.id,
      title,
      state
   ORDER BY avg_rating DESC

You used join, which is short for inner join. This type of join only gives results if a matching record exists in both tables. Change it to left join (short for left outer join), to also include listings without reviews.

You will need to move the state check and any other check to the join condition too, otherwise those listings without review will be dropped from the result again.

Lastly, you can coalesce the average value to get 0 instead of null for those records.

   SELECT
     ls.id,
     title,
     state,
     COALESCE(ROUND(AVG(rating),2), 0) avg_rating
   FROM listing ls
   LEFT JOIN review rv
   ON ls.id = rv.listing_id 
     AND ls.state = '$get_state'
     AND ls.mail_approved = 1
   GROUP BY ls.id,
      title,
      state
   ORDER BY avg_rating DESC

As a side note, please check prepared statements (for PDO or MySQLi) for the proper way to pass input parameters to your query instead of concatenating with variables like $get_state. Concatting is error prone, and makes you more vulnerable for SQL injection.

Outer join the avarage ratings to the hotels:

select
  l.id,
  l.title,
  l.state,
  coalesce(r.avg_rating, 0)
from listing l
left join
(
  select
    listing_id,
    round(avg(rating), 2) as avg_rating
  from review
  where mail_approved = 1
  group by listing_id
) r on r.listing_id = l.id
where l.state = '$get_state'
order by avg_rating desc;