[FIXED] Search for product ids where an attribute is not present

Issue

I am using opencart for an online store and I have a SQL structure like this: (image from phpmyadmin)

I am trying to cross match product ids with attribute ids. I need to find products that don't have a particular attribute_id (attribute_id 17 to be more precise).

I tried sorting and exporting in various formats without success. I am not good with mysql syntax but I am sure there has to be a way to achieve this result.

Also tried using this code:

SELECT product_id FROM oc_product_attribute WHERE NOT EXISTS (SELECT * FROM oc_product_attribute WHERE attribute_id = 17) (oc_product_attribute is the table name)

...but it didn't output any results.

Please help me understand how I can find the product IDs that don't have attribute ID 17.

Thanks!

Solution

You should have a product table (in your case probably oc_product). Use it to avoid multiple checks. Also there might be a product which has no attributes. And you would miss that product in the result, if you only use the attributes table.

There are two common ways to achieve your goal. One is using a LEFT JOIN:

select p.*
from oc_product p
left join oc_product_attribute a
  on  a.product_id = p.product_id
  and a.attribute_id = 17
where a.product_id is null

It's important that the condition a.attribute_id = 17 is in the ON clause. If you use it in the WHERE clause, the LEFT JOIN would be converted to an INNER JOIN, and you would get an empty result.

The other way is to use a correlated NOT EXISTS subquery:

select p.*
from oc_product p
where not exists (
    select *
    from oc_product_attribute a
    where a.product_id = p.product_id
      and a.attribute_id = 17
)

Note the (correlation) condition a.product_id = p.product_id. If you miss it (like in your attempt) the subquery will always find a row, and NOT EXISTS will always return FALSE.

Both approaches have similar performance.

If you only need the product ids you can replace p.* with p.product_id.

Answered By - Paul Spiegel