SQL query to search several rows with same id field but different parameter field
I feel that this should be simple, but I'm stuck. This is what I have in my DB
id field_value ---------------- 41 Blue 42 Blue 43 Blue 43 Sweet 46 Blue 47 Sweet
I need to select fields that only have $colour = blue AND $taste = sweet. Basically I need to get the id of 43 because it has both Blue and Sweet. How can I do that?
You would usually test it like this:
select id from table1 where field_value in ('Blue', 'Sweet') group by id having count (distinct field_value) = 2
= 2 is number of items in in () list.
If I understand correctly; Something like this:
$colour = (!isset($_GET['colour']) || empty($_GET['colour'])) ? '' : $_GET['colour']; $taste = (!isset($_GET['taste']) || empty($_GET['taste'])) ? '' : $_GET['taste']; $query = "SELECT * FROM table WHERE (parameter = '$colour') AND (parameter = '$taste')";
Use a query within a query. Something like;
SELECT id FROM table WHERE field_value = 'Blue' AND id IN (SELECT id FROM table WHERE field_value = 'Sweet') GROUP BY id