2

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!

  • 3
    Good morning. You may need to explain what is unexpected about the result. – Nicolas de Fontenay Nov 3 '15 at 16:18
  • 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 ... – mpag Nov 3 '15 at 21:51
  • 1
    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? – Andriy M Nov 4 '15 at 9:56
  • Yes, I had the same mistake when trying it. Unfortunately the change didn't fix the issue :( – kanenas Nov 4 '15 at 11:10
  • 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? – mpag Nov 4 '15 at 14:39
2

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

  • 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) – kanenas Nov 4 '15 at 8:26
  • 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? – Andriy M Nov 4 '15 at 10:45
  • 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 Nov 4 '15 at 14:43
  • 1
    @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. – mpag Nov 4 '15 at 14:47
  • Can't you get rid of the subquery and simply do a LEFT JOIN? – Rick James Dec 4 '15 at 20:38
1

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.

  • 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! – kanenas Nov 3 '15 at 16:34
  • Can't you get rid of the subquery and simply do a LEFT JOIN? – Rick James Dec 4 '15 at 20:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.