如何用一个查询从一个表中选择多个键值？

Question

this is sample product properties table

product_id  property_id property_value
5           1           white
5           2           50
5           3           50
5           4           55
5           5           mm
6           8           cm

i want filter my products dynamically. for example:

select property_id 1 and property_value white
AND
select property_id 2 and property_value 50
AND
select property_id 4 and property_value 55
AND
etc ...

i can make dynamic query from a basic query. when i use all conditions together, no record matched because all conditions operator is AND. what is the best query? thanks for your answers.

Answer 1

It sounds like you're using the wrong keyword. Use OR instead of AND between the sets of criteria:

SELECT DISTINCT product_id

FROM product_properties

WHERE
(property_id = 1 and property_value = 'white')
OR
(property_id = 2 and property_value = '50')
OR
(property_id = 4 and property_value = '55')
OR
etc ...

You can use DISTINCT if you just want one of each product_id that matches the various criteria, or leave it off to get one for each row that matches.