First let me explain the table stucture and test data.

Simple table:
CREATE TABLE drinks(drinkid INT, ingid INT)

Table Contents
[1,3]
[1,4]
[2,3]
[2,4]
[2,5]
[3,4]

I have the following sql statement which will select out all drinks that can be made from any subset of the specified ingredient id's and do not contain any ingredients that I did not specify:

SELECT DISTINCT drinkid
FROM drinks
WHERE drinkid NOT IN
(SELECT drinkid
FROM drinks
WHERE ingid NOT IN (1, 3, 4))

The above query would return the drinkid's 1 and 3. It would not return drinkid 2 because it contains the ingredient 5, which was not in my list of specified ingredients.

My problem is I cannot find a way to rewrite this query in such a way that it will run on mysql 4.0.x (ie, without the subquery)... please help!

Thanks
-Justin

A series of SQL statements can do the trick.....

create temporary table my$temp
select distinct drinkid
from drinks
where ingid in (1,3,4);

insert into my$temp
select distinct drinkid
from drinks
where ingid not in (1,3,4);

select drinkid from my$temp
group by drinkid having count(*) < 2;

drop table my$temp;


-Mike