0

I want to retrieve from my table the id_product of the products which haven't got some attribute and it's value is not the ones i want

For example i want the id_product of the products in which attribute = 'Product Type' and value != 'Motherboard' AND value != 'Intel Motherboard'

I have the following table: Atr_basic

column         | type                 | attributes
---------------+----------------------+-----------------------------------------------------------
id             | int                  | (identity) primary key
attribute      | varchar              | not null
value          | varchar              | not null
category       | int                  | not null  
id_product     | int                  | not null

The query:

SELECT id_product 
FROM Atr_basic 
WHERE attribute = 'Product Type' 
      AND value != 'Motherboard' AND value != 'Intel Motherboard' AND category = 140

The problem with this query it's only give me the id_product from the products they have Attribute = 'Product Type' but some product doesn't have Attribute = 'Product Type'

I managed to do this query but it takes 120seconds to complete:

SELECT id_product 
FROM Atr_basic as ab
WHERE id_product 
    NOT IN (SELECT a.id_product 
                   FROM Atr_basic as a
                   WHERE a.attribute = 'Product Type' 
                   AND a.value != 'Motherboard' 
                   AND a.value != 'Intel Motherboard' 
                   AND Category = 140)
1
  • I think you need indexes on attribute, value and category columns
    – cha
    Feb 26, 2015 at 22:22

1 Answer 1

0

You are missing the single quote closure after motherboard in both of your queries

AND value != 'Motherboard AND

AND a.value != 'Motherboard

The first query can be more organized as

SELECT id_product 
FROM Atr_basic 
WHERE attribute = 'Product Type' 
AND category = 140
AND value NOT IN ( 'Motherboard', 'Intel Motherboard') 
1
  • Compound INDEX(attribute, category) will help performance. Getting rid of the IN(SELECT...) led to the huge speedup.
    – Rick James
    Feb 27, 2015 at 17:56

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.