Using "AND" with "IN" - is this possible?

[miahmiah900]

This is, I think, a strange question. I have solved the problem using a different method, but I would like to know if anyone has any input on the following:

I am performing a SELECT similar to the following:

SELECT inventory_id FROM tbl_option_key WHERE data_id IN (3,7)

What this gives me is an inventory_id for each row that contains EITHER 3 or 7 in the data_id column. What I would like to do, is to have a query that returns the inventory_id only if it matches ALL entries in the "IN" list.

This may seem illogical, but the reason I want to do this is because there needs to be 0 or many entries in tbl_option_key for each inventory_id.

The method I used to solve this method is a GROUP BY on the inventory_id field and counting the number of matches to see if the total is the same as the number of entries in the "IN" list.

Any suggestions?

Thanks!

-miah

 

[sleipnir214]

There is no conjunctive version of IN.

The only way I know of is to construct a set of ANDs:

WHERE data_id = 3 AND data_id = 7.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[swampBoogie]

select inventory_id 
FROM tbl_option_key 
WHERE data_id IN (3,7)
group by inventory_id
having count(distinct data_id) = 2