Join with multiple queries in SQL

Question

I have this query already:

SELECT * FROM (SELECT * FROM `prefix_messages`
                        WHERE `category_id`=4
                        ORDER BY `id` DESC LIMIT 30) ilv
         ORDER BY `id` ASC

How to use join query to add data from users table if in prefix_messages I have user_id column?

Thanx!

How can you imagine an answer without any knowledge of tables schema? — sll
– sll, Commented Aug 9, 2011 at 21:54

Abe Miessler · Accepted Answer · 2011-08-09 21:56:12Z

1

Try this:

SELECT * FROM   (SELECT pm.ID as prefixID, * FROM prefix_messages as pm
    INNER JOIN users as u
    ON pm.User_id = u.UserId
    WHERE pm.category_id=4
    ORDER BY pm.id DESC LIMIT 30)
    ORDER BY prefixID ASC

I assume that you are only doing the subselect because you want to reverse the order after you get the top 30?

answered Aug 9, 2011 at 21:56

Abe Miessler

85.6k104 gold badges323 silver badges496 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Join with multiple queries in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related