Query with WHERE NOT and NOT IN with subquery

Question

MySQL Version: 5.6.27

I am using the following two tables

TABLE: oc_product
product_id int(11)
model varchar(64)
tax_class_id int(11)

and

TABLE: oc_product_to_category
product_id int(11)
category_id int(11)

I want to change the tax_class_id for a few products on TABLE oc_product. So I am running a SELECT query before UPDATE

SELECT `product_id`, `model`, `tax_class_id` 
FROM `oc_product` 
WHERE 
NOT `tax_class_id` = 12 
AND
`product_id` 
NOT IN ( SELECT `product_id` 
 FROM `oc_product_to_category` 
 WHERE `category_id` 
 IN ( 73, 78, 116, 119, 120, 121, 122, 123, 125, 126, 127, 143, 159, 170, 176, 183, 211, 212, 213, 250, 260, 389, 433) 
 )

Unfortunately the results are not as expected.

All I want is to select all product_id from the table oc_product where tax_class_id is not 12 and product_id does not exist in the results of the following sub query.

SELECT `product_id` 
FROM `oc_product_to_category` 
WHERE `category_id` 
IN ( 73, 78, 116, 119, 120, 121, 122, 123, 125, 126, 127, 143, 159, 170, 176, 183, 211, 212, 213, 250, 260, 389, 433)

Did I miss something? Please advise!

• Nicolas de Fontenay: Good morning. You may need to explain what is unexpected about the result.
• mpag: in your inner select you'll probably want to explicitly define what table the product_id is coming from. I'd do this by aliasing oc_product_to_category AS p2c and SELECTing p2c.product_id. you could change the product_id NOT IN to NOT EXISTS (SELECT 1 FROM (SELECT p2c.product_id ...
• Andriy M: So you've just corrected code in your post. Did you have the same mistake when you were trying it? Did the change fix the issue?
• kanenas: Yes, I had the same mistake when trying it. Unfortunately the change didn't fix the issue :(
• mpag: so what is "the issue"? what is being returned that shouldn't be returned? or what is not being returned that "should" be returned? If your dataset is too complex to summarize, then please use the test data that I pasted and/or identify which of the test set rows you want returned. You say "and product_id does not exist in the results of the following sub query." - the problem, I suspect is with what you expect the subquery to return...so what do you expect that subquery to return?

Answer 1

Your unexpected results may be stemming from product_id being assigned to multiple categories in the oc_product_to_category table. If a product is in e.g. category 1 AND category 73, it will still be returned by the inner query and thus excluded from the outer query.

Produce a simple set of test data

INTO oc_product INSERT (product_id, model, tax_class_id) VALUES
(1, 'model5', 10),
(2, 'AA', 5),
(3, '12', 8),
(4, '14', 12),
(5, 'xx', 12)
INTO oc_product_to_category INSERT (product_id, category_id) VALUES
(1, 1), -- product_id is in a category that is not in that list
(1, 73), -- product_id is STILL in a category that is not in that list; as well as a value in that list
(2, 73), -- product_id is only in categories in that list
(3, 17), -- product_id is only in categories not in that list
(4, 33), -- product_id is only in categories not on the category list, is excluded because of tax category
(5, 73), -- product_id is only in categories in the list, is excluded because of tax category

(I probably have the syntax of the INSERT query backwards;) )

I believe your current query will return

(2, 'AA', 5),

You probably want something like

SELECT 
 op.`product_id`,
 op.`model`,
 op.`tax_class_id` 
FROM
 oc_product AS op
 LEFT JOIN
 (SELECT DISTINCT p2c.product_id
 FROM oc_product_to_category as p2c
 WHERE
 p2c.category_id IN (73, 78, 116, 119, 120, 121, 122, 123, 125, 126, 127, 143, 159, 170, 176, 183, 211, 212, 213, 250, 260, 389, 433)
 ) as OnlyProductsBelongingToTheseIncluded
 ON op.product_id = OnlyProductsBelongingToTheseIncluded.product_id
WHERE 
 ( op.tax_class_id <> 12 ) AND
 OnlyProductsBelongingToTheseIncluded.product_id IS NOT NULL

This should return

(1, 'model5', 10),
(2, 'AA', 5)

3 is excluded because it only exists in other groups; 4 and 5 are excluded because of the tax code

Changing the last line to AllProductsBelongingToTheseExcluded.product_id IS NULL (and the appropriate subquery alias) should return only the (3, '12', 8) datapoint

• kanenas: you are right about products being in more than one categories. The problem is that with your query as with mine I keep getting results for products that shouldn't be in results NOT IN ( 73, 78, 116, 119, 120, 121, 122, 123, 125, 126, 127, 143, 159, 170, 176, 183, 211, 212, 213, 250, 260, 389, 433)
• Andriy M: LEFT JOIN A ... WHERE A.ID IS NOT NULL is essentially the same as INNER JOIN A. Maybe you meant IS NULL rather than IS NOT NULL?
• mpag: Again, is the problem with products that are in multiple categories? Or is that not possible because there a unique key enforcement on the oc_product_to_category table?
• mpag: @AndriyM yes those are essentially the same, but this one is more easy to modify to produce the "opposite" results. The main problem is that kanenas is being too vague as to what he/she is expecting with what data, so I can only speculate as to what they want.
• Rick James: Can't you get rid of the subquery and simply do a LEFT JOIN?

Answer 2

AS an alternative, you could try a left join on oc_product.

SELECT a.`product_id`, a.`model`, a.`tax_class_id` 
FROM `oc_product` a
left join
( SELECT `product_id` 
 FROM `oc_product_to_category` 
 WHERE `category_id` 
 NOT IN ( 73, 78, 116, 119, 120, 121, 122, 123, 125, 126, 127, 143, 159, 170, 176, 183, 211, 212, 213, 250, 260, 389, 433)
) b
on a.product_id=b.product_id
WHERE 
a.`tax_class_id` != 12 
and b.product_id is null

So with the left join, you grab every rows from oc_product. You join it on product_id to your subquery. it will display everything from oc_product, but will displays rows from your subquery only when product_id is a match, leaving b.product_id null when there's no match.

You can then filter only b.product_id null values and you get the product_id you are looking for.

This approach can also be faster at times, depending on your tables and indexes.

If the results are still not matching, you may want to consider what's wrong with the data itself. The query logic is good.

• kanenas: your query gives me the same "not expected" results as mine. So as you suggested I will have a closer look with the data itself and I will get back to you. Thanks!
• Rick James: Can't you get rid of the subquery and simply do a LEFT JOIN?