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.
Best Answer
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:
That way the first version of the table (
pa1
) matchestext='wi-fi'
, the second version(pa2
) matchestext='bluetooth'
, and theJOIN
condition (pa1.product_id = pa2.product_id
) ensures both conditions are met for a single product.