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