phpmyadmin Question posted in
The official documentation can be found here.

Search for product ids where an attribute is not present – Phpmyadmin

I am using opencart for an online store and I have a SQL structure like this:
data
(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!

Tags:

2

Answers


  1. 0

    Your current approach is on the right track, but you need to correlate the exists subquery with the outer query:

    SELECT DISTINCT o1.product_id
FROM oc_product_attribute o1
WHERE NOT EXISTS (SELECT 1 FROM oc_product_attribute o2
                  WHERE o1.product_id = o2.product_id AND o2.attribute_id = 17);

    We could also use an aggregation approach here:

    SELECT product_id
FROM oc_product_attribute
GROUP BY product_id
HAVING COUNT(attribute_id = 17) = 0;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search