Mysql – Joining two select statements

greatest-n-per-groupjoin;MySQLmysql-8.0

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.

Best Answer

You can use the 2nd query as a subquery in this way:

SELECT 
    usr1.name,
    posts.id,
    text_posts.text,
    posts.location
    posts.createdate
    COUNT(*) AS total,
    (SELECT 
        COUNT(user_id) 
    FROM 
        follows 
    INNER JOIN 
        users usr2 ON usr2.id = follows.user_id
    WHERE 
        usr2.id = usr1.id  
    GROUP BY 
        user_id) as totalFriends 
FROM
    posts
INNER JOIN 
    likes ON likes.posts_id = posts.id
INNER JOIN 
    users usr1 ON photos.user_id = usr1.id
INNER JOIN 
    text_posts ON text_post.posts_id = posts.id
GROUP BY 
    posts.id
ORDER BY 
    total DESC
Related Question