IN statment

Sergey_S wrote: I have the table:     ID condition_id property_id 1 c_3 b_1 2 c_13 b_2 3 c_3 b_4 4 c_13 b_1 5 c_3 b_2 6 c_4 b_1 I need to select all properties with condition_id c_3 and c_13 not just c_3 or c_13 (I expect in the result it should be only b_1 and b_2) The query like this: SELECT * FROM table WHERE condition_id IN (  3,13                                                            )

A simple way to do it is:

SELECT *

FROM table

WHERE condition_id IN ('c_3') AND property_id IN (SELECT property_id FROM table WHERE condition_id IN ('c_13'))

Your question (or English) was a bit vague, but here goes:

If you want just c_3 and c_13's returned

SELECT id,condition_id,property_id

FROM table

WHERE condition_id = 'c_3' OR condition_id = 'c_13'

But it seemed you wanted to filter out the results further by having only c_3 and c_13's which also match b_1 or b_2

SELECT id,condition_id,property_id

FROM table

WHERE (condition_id = 'c_3' OR condition_id = 'c_13')

AND (property_id = 'b_1' OR property_id = 'b_2')

Alternatively,

SELECT id,condition_id,property_id

FROM table

WHERE condition_id IN ('c_3,'c_13')

AND property_id IN ('b_1','b_2')

-Fernis

Can you show us your exact cfquery statement?  Hard to believe those results!  The actual syntax you want would be:

SELECT *

FROM table

WHERE condition_id='c_3' AND condition_ID='c_13')

The IN operator is just a shortcut for a bunch of OR operators.

hth

Reed