vendredi 27 février 2015

Query results that not are in previous query result


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)




Aucun commentaire:

Enregistrer un commentaire