Im using MySql 8.0 and I've been exercising a lot of quries but this one I cant seem to figure it out. I need to join somehow two select statements that I've written and they display the correct results. But I dont know how to join them so I will get the desired result.
SELECT ... FROM ( SELECT users.name, posts.id, text_posts.text, posts.location posts.createdate COUNT(*) AS total FROM posts INNER JOIN likes ON likes.posts_id = posts.id INNER JOIN users ON photos.user_id = users.id INNER JOIN text_posts ON text_post.posts_id = posts.id GROUP BY posts.id ORDER BY total DESC ) as A join (select username, user_id, count(user_id) as totalfriends FROM follows INNER JOIN users ON users.id = follows.user_id group by user_id order by totalfriends desc ) as B ON A.posts.id = B.posts.id LIMIT 10;
The result should be 10 posts but the user needs to have the most amount of friends and that those posts the users created have the most likes, and also display the name of the creator of that post, the content of the post, location, date of creation and the number of likes so basically the two subqueries put together.