Retrieve multiple records based on multiple AND WHERE conditions
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.
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
This sounds like a UNION of different queries.
SELECT ... WHERE ...
UNION
SELECT ... WHERE ...
UNION
SELECT ... WHERE ...
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)
...
精彩评论