hi-

i have 3 tables, students, preferences, and food. students is a
simple table of students:

studentID name

food is a simple table of food:

foodID description

preferences links the two, representing "likes" relationship:

preferenceID studentID foodID

what I want to do is, find all of the other foods that a set of
students like if they like a particular food. in other words, what i
want to do, for example, is find out what other foods do kids who like
blueberries like (by count)? so, i first want to find all the kids who
like blueberries, then given that set of studentIDs, i want to
histogram the preferences table by food (you can assume there are no
duplicates).

so, what i've tried is this:

select f.description, count(*) from food F, preferences P, students S
where
P.studentid=s.studentid and
P.foodid=f.foodid and
F.description like '%blueberries%'
Group By F.FoodID

this almost works, but it *only* gives me rows where the food is
blueberries. i want to count what *else* you like IF you like
blueberries, and if blueberries is included, that's fine.

what i want is something more like:

select F.description, count(*) from students S, Preferences P, Food F
where S.studentID in
(
select S.studentid from students S, Preferences P, Food F
where
S.studentID=P.studentID and
P.foodID=F.FoodID and
f.description like '%blueberries%' /* so
these are all the kids who like blueberries*/
)
and
S.studentID=P.studendID and
P.foodID=F.foodID
group by F.foodID /*and this is
the histogram of all of these kids preferences*/

but, this gives me all sorts of errors. I cannot get a subquery to
work with mySQL, i keep getting a 1064
error ( version 5.0.18 i think, that's what php_info() gives as the
"client version").

thanks for any ,
D.