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.

  • i only do see 1 query?.... if both queries return the same number of colums, you can do SELECT * FROM (query_a UNION query_b) AS result. (Replace query_a with your first query, and query_b with the second query. – Luuk Dec 27 '19 at 17:58
  • I did an edit saying 8.0, and also that isnt the case @Luuk because here are two select statements inside one big select, and those nested select statements, one returns the users ranked by the amount of friends they have and the other one returns the likes of the post (descending), the name of the creator of the post, the date the post has been made and the location – Engjell Berisha Dec 27 '19 at 18:06
  • @McNets that was a typo, it was meant to be posts, but still I dont believe it will work, because I dont have posts on table B, because table B is there to show us the users ranked by the amount of friends they have. – Engjell Berisha Dec 27 '19 at 18:27
  • Sounds like a "groupwise-max" problem. – Rick James Dec 28 '19 at 6:59
0

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 
  • So this query will help me output top 10 users with the most friends and that the posts have the most likes? – Engjell Berisha Jan 8 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.