Retrieve multiple records based on multiple AND WHERE conditions

Question

I am currently struggling with a query that needs to retrieve multiple records from my table based on multiple WHERE clauses. Each WHERE clause contains two conditions.

Table layout:

+--------------+---------+------+-----+---------+----------------+
| Field        | Type    | Null | Key | Default | Extra          |
+--------------+---------+------+-----+---------+----------------+
| id           | int(11) | NO   | PRI | NULL    | auto_increment |
| entity_id    | int(11) | YES  | MUL | NULL    |                |
| attribute_id | int(11) | YES  |     | NULL    |                |
| value        | int(11) | YES  |     | NULL    |                |
+--------------+---------+------+-----+---------+----------------+

What I need to retrieve:

One or more records matching an array of attribute_id's with corresponding values. In this case, I have an array with the following structure:

array(
   attribute => value,
   attribute => value,
   attribute => value
)

The problem:

I cannot cycle through the array and create WHERE clauses for this query, since each WHERE condition would automatically negate the other. All attribute/value pairs should be matched.

I almost thought I had the solution with this query:

SELECT `client_entity_int`. * 
FROM `client_entity_int` 
WHERE (attribute_id IN (1, 3)) 
HAVING (value IN ('0', '3'))

... but obviously, this would retrieve both values for both attributes, where I just need attribute 1 to be 0 and attribute 3 to be 3.

Any help would be appreciated.

Answer 1

This sounds like a UNION of different queries.

SELECT ... WHERE ...
UNION
SELECT ... WHERE ...
UNION
SELECT ... WHERE ...

Answer 2

If you use an OR, not every WHERE clause will negate the other :)

For example:

WHERE (attribute_id = 1 and value = '0')
OR    (attribute_id = 3 and value = '3')

To insist that ALL conditions match, count the matches:

WHERE    (attribute_id = 1 and value = '0')
OR       (attribute_id = 3 and value = '3')
GROUP BY entity_id 
HAVING   COUNT(*) = 2

Answer 3

would this work?:

SELECT `client_entity_int`. * 
FROM `client_entity_int` 
WHERE (attribute_id=1 AND value=0) OR (attribute_id=3 AND value=3)
...