On Sep 19, 4:52 pm, "dino d." <dinodorr...@yahoo.com> wrote:
> 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.

You keep getting "all sorts of error." If you told us what those
error messages said we might be able to you.