I've got one main users database table and a "meta" table to outer join. Basically the essential query is
我有一个主用户数据库表和一个“meta”表到外部连接。基本的查询是
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.
但是我必须限制users_table ID列上的结果。如果我使用标准的LIMIT子句,查询也将计数元值。
Would be possible to limit on the user's table ID column and associate metas with the join?
是否可能限制用户的表ID列并将元数据与连接关联?
2 个解决方案
#1
3
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
#2
1
Thanks to @mergenchik to point me on the right direction.
感谢@mergenchik为我指明了正确的方向。
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
我最后得到了一个稍微不同的查询,以在开始时保留users表数据,并能够在将来加入一个新的表
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
#1
3
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
#2
1
Thanks to @mergenchik to point me on the right direction.
感谢@mergenchik为我指明了正确的方向。
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
我最后得到了一个稍微不同的查询,以在开始时保留users表数据,并能够在将来加入一个新的表
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