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?

Answers


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.

Check out the test @ Sql Fiddle.


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

Need Your Help

Could you share a link to an URL parsing implementation?

java string parsing url scala

As far as I understand, an URL consists of the folowing fields:

Unable to access GET parameters in JSF backing bean

jsf-2 managed-bean

I am unable to access GET parameters in a backing bean. The example.xhtml file: