开发者

Retrieve multiple records based on multiple AND WHERE conditions

开发者 https://www.devze.com 2022-12-12 11:40 出处:网络
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.

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)
...
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号