mysql查询带limit_MySQL选择带有子查询和LIMIT

I am using the subselect to get the row IDs I need like this:

SELECT

p.id, c.id as category_id

FROM

(SELECT id FROM products p WHERE p.id > 6319055 ORDER BY id LIMIT 1000) prods

LEFT JOIN

products p ON p.id = prods.id

LEFT JOIN

categories c ON (c.id = p.category_id)

WHERE

c.active = 1

The ID 6319055 is my last selected ID. I save it after selecting the data.

Now the problem I am having is that I am selecting 1000 rows on each cycle and at some point I select 1000 rows which doesn't meet the

WHERE c.active = 1

requirements. Select returns nothing and I don't have any row ID to continue the subselect.

Any ideas how could I solve this? How can I get the last ID of the sub select, even if it doesn't meet the WHERE clause?

解决方案

When you use WHERE condition on the right-side table of a LEFT JOIN (Outer Join), it effectively becomes an INNER JOIN, because WHERE clause needs to match the conditions. That is why you are only getting cases where c.active = 1.

You need to shift the WHERE condition to LEFT JOIN .. ON .. AND .. condition:

SELECT

p.id, c.id as category_id

FROM

(SELECT id FROM products p WHERE p.id > 6319055 ORDER BY id LIMIT 1000) prods

LEFT JOIN

products p ON p.id = prods.id

LEFT JOIN

categories c ON c.id = p.category_id

AND c.active = 1