Search code examples
mysqlsqlselectjoininner-join

select all and avg from joined table

Please could you help to get the query for described situation below:

1 TABLE: shops:    id | name | is_locked   |
2 TABLE: products: id | name | price       | shop_id 
3 TABLE: rates:    id | rate | is_accepted | shop_id

I've got 3 tables with shops, shop's products and rates. And now I would like to sort all products, which belong to the not locked shop and order by average of the accepted shop rates. How should this query look like?

Solution

  • You could try this:

    select p.name, p.price, AVG(r.rate) 
from shops s, products p, rates r 
Where s.is_locked <> 1 and
      s.id = p.shop_id and
      s.id = r.shop_id and
      p.shop_id = r.shop_id
group by p.id 
order by AVG(r.rate)