How to get the value of column that the same for few rows by selecting values from other columns?

Question

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.

Answer 1

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.

Answer 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!