How to select multiple tables in one sql query?

Question

SQL

Hello everyone, I have such a task (You need to sort establishments by rating from their reviews, if the establishment is a chain (for example, McDonald's), then you need to consider its rating as the arithmetic mean of the ratings of its branches, if the establishment is a branch, you do not need to display it, since its rating is included in the rating of the network, if the institution does not have a review, then its rating will be 0.

For networks, it is necessary to consider the arithmetic mean of the ratings of branches and not the arithmetic mean of the review ratings of all branches,
if the branch has an odd number of reviews, such a calculation will not be correct) . Please tell me in which direction I need to move, because I am new to sql queries and such a query is a problem for me at this stage. I would be grateful for any help.

Answer 1

SELECT 
  chains.name,
  ROUND(AVG(rate), 2) avg_rate
FROM Establishment
JOIN Review ON Review.establishment_id = Establishment.id
JOIN Rate ON Rate.review_id = Review.id
JOIN Establishment chains ON chains.id = COALESCE(Establishment.chain_id, Establishment.id)
GROUP BY chains.name
ORDER BY avg_rate DESC
;

SQL fiddle live