Search code examples
mysqlsqlentity-relationship

SQL - How to select the products that have received the most like?

I have two table

Product(id, ..., other attribute...)

in relation with

UserLikeProduct(id, product_id, like_at, user_id)

How do I select the Product s that have received the most like?

Solution

  • First, you need to build a result of user likes ordered from high to low

    select product_id,count(*) as TotLikes
from UserLikeProduct
group by Product_id

    Now join this with the product table

    select p.Product_id,tl.TotLikes * 
from Products p
join *
(     select product_id,count(*) as TotLikes
    from UserLikeProduct
    group by Product_id

) tl on tl.Product_id=p.Product_id
order by 2 DESC