1

Since I am not a MySQL expert, I am having trouble determining how to ask a search engine for advice on this topic.

I have the following table:

+------------+--------------+-------------------+
| product_id | attribute_id | text              |
+------------+--------------+-------------------+
|       1770 |           57 | wi-fi             |
|       1770 |           58 | bluetooth         |
+------------+--------------+-------------------+

There are many other products with many attributes.

I need to select product_id that has text = wi-fi and text = bluetooth (in different rows). The table name is product_attribute.

I have tried the following:

SELECT * 
FROM product_attribute 
WHERE product_attribute.text = 'wi-fi' 
    AND product_attribute.text = 'bluetooth';

However no results are returned since in one row we have text = wi-fi and another row has text = bluetooth, whereas my query is looking for both wi-fi and bluetooth in the same row.

How can I get product_id = 1770 by making the query select from the text column in different rows with the same product_id?

Here is SQL Fiddle.

2

Your query is close but doesn't work because no row will satisfy both text='wi-fi' AND text='bluetooth' at the same time.

You could use a self join instead:

SELECT
  pa1.*
FROM
  product_attribute pa1
  JOIN
  product_attribute pa2
  ON pa1.product_id = pa2.product_id
WHERE
  pa1.text = 'wi-fi'
  AND
  pa2.text = 'bluetooth'
;

That way the first version of the table (pa1) matches text='wi-fi', the second version(pa2) matches text='bluetooth', and the JOIN condition (pa1.product_id = pa2.product_id) ensures both conditions are met for a single product.

2

This may work:

SELECT product_attribute.product_id
FROM product_attribute 
WHERE product_attribute.text = 'wi-fi' 
    OR product_attribute.text = 'bluetooth'
GROUP BY product_attribute.product_id
HAVING COUNT(*) = 2;

The idea here is to use an OR in the WHERE clause to allow rows that have either wi-fi or bluetooth in the text column, then we only return products that have at least two rows.

Thanks to @jkavalik for creating a test sqlfiddle that works, and proves the above code!

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.