I have two mysql database table's posts and post_likes...I want to get all posts which has more than 250 likes

right now i am using this query :-

SELECT posts.*, @total_likes := COUNT(nft_likes.id) as total_likes FROM posts inner join nft_likes on nft_likes.nft_id=posts.auction_id where @total_likes>1 group by posts.id

This is the first time i have asked a question.so pls forgive for bad way of telling

post_likes table schema

post table schema

CodePudding user response：

In the WHERE clause you can only refer to a row's data. The result of a COUNT, however, refers to the aggregation of several rows. Use the HAVING clause for limitations on these results.

SELECT 
  p.*, 
  COUNT(l.id) AS total_likes
FROM posts p
INNER JOIN nft_likes l ON l.nft_id = p.auction_id 
GROUP BY p.id
HAVING COUNT(l.id) > 1
ORDER BY p.id;