CODEWITHSUNDEEP
mysqlleft-joinlimitouter-join
a-coder
a-coder

Reputation: 289

MySQL query: use LIMIT on one table column having an OUTER JOIN

I've got one main users database table and a "meta" table to outer join. Basically the essential query is 

SELECT * FROM users_table 
LEFT OUTER JOIN meta_table ON users_table.id = meta_table.user_id 
ORDER BY users_table.id ASC

But I have to limit results on the users_table ID column. If I use the standard LIMIT clause, the query will count also meta values.

Would be possible to limit on the user's table ID column and associate metas with the join?

Upvotes: 3

Views: 431

Answers (2)

a-coder
a-coder

Reputation: 289

Thanks to @mergenchik to point me on the right direction.

I ended up with a slightly different query to keep users table data at the beginning and be able to join a new table in future 

SELECT * FROM 
    (SELECT * FROM users_table ORDER BY id ASC LIMIT 15) as users_table
LEFT OUTER JOIN meta_table 
ON meta_table.user_id = users_table.id

Upvotes: 1

mergenchik
mergenchik

Reputation: 1139

You can try to use subquery. For example

SELECT * FROM meta_table 
         RIGHT OUTER JOIN (SELECT * FROM users_table 
                           WHERE status = 1
                           ORDER BY users_table.id ASC LIMIT 10) as tbl_user 
         ON meta_table.user_id = tbl_user.id

Upvotes: 4

Related Questions