1

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?

The50
  • 1,096
  • 2
  • 23
  • 47
  • This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. We cannot reason, communicate or search unless we make the effort to (re-re-re-)write clearly. – philipxy Sep 14 '19 at 02:41
  • Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. – philipxy Sep 14 '19 at 02:42

1 Answers1

1

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
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • Yes, but I need this INNER JOIN, I don't need these inactive category products. I want to skip them till the next products which meets the c.active clause. – The50 Sep 12 '19 at 18:24
  • 1
    @The50 if I understand you correctly, even your current query cannot ensure 1000 products (even if there are more than 1000 products available with category active), because you are not doing the category filter in the subquery (where you get 1000 product id(s)) – Madhur Bhaiya Sep 12 '19 at 18:28
  • 1
    Here we go, perfect idea, thank you. :) I joined the categories table into the subquery and selected only the ID's which meets the category requirement. – The50 Sep 12 '19 at 18:39