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.

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.

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


Sorry-

Here are some specifics:

the tables:

CREATE TABLE `students` (
`studentid` INT NOT NULL AUTO_INCREMENT ,
`name` TEXT NOT NULL ,
PRIMARY KEY ( `studentid` )
) TYPE = MYISAM ;

CREATE TABLE `preferences` (
`preferenceid` INT NOT NULL AUTO_INCREMENT ,
`studentid` INT NOT NULL ,
`foodid` INT NOT NULL ,
PRIMARY KEY ( `preferenceid` )
) TYPE = MYISAM ;

CREATE TABLE `food` (
`foodid` INT NOT NULL AUTO_INCREMENT ,
`description` TEXT NOT NULL ,
PRIMARY KEY ( `foodid` )
) TYPE = MYISAM ;


and then the query:

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%'
)
AND S.studentID = P.studentID
AND P.foodID = F.foodID
GROUP BY F.foodID

and the result:

MySQL said:
#1064 - You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'select S . studentid from students S , Preferences P , Food F

the subquery by itself works fine, and if i substitute the subquery
with a set of scalars, it also works fine.

and just to make sure my version supports subqueries, here's what
php_info says:

mysql
MySQL Support enabled
Active Persistent Links 0
Active Links 0
Client API version 5.0.18
MYSQL_MODULE_TYPE external
MYSQL_SOCKET /tmp/mysql.sock
MYSQL_INCLUDE -I/usr/local/mysql/include
MYSQL_LIBS -L/usr/local/mysql/lib -lmysqlclient

thanks for taking a look at this, i appreciate it.

Dino

On Thu, 20 Sep 2007 02:12:08 -0700, dino d. wrote:
> and then the query:
>
> 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%'
> )
> AND S.studentID = P.studentID
> AND P.foodID = F.foodID
> GROUP BY F.foodID
>
> and the result:
>
> MySQL said:
> #1064 - You have an error in your SQL syntax. Check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near 'select S . studentid from students S , Preferences P , Food F
>
> the subquery by itself works fine, and if i substitute the subquery
> with a set of scalars, it also works fine.
>
> and just to make sure my version supports subqueries, here's what
> php_info says:
>
> mysql
> MySQL Support enabled
> Active Persistent Links 0
> Active Links 0
> Client API version 5.0.18
> MYSQL_MODULE_TYPE external
> MYSQL_SOCKET /tmp/mysql.sock
> MYSQL_INCLUDE -I/usr/local/mysql/include
> MYSQL_LIBS -L/usr/local/mysql/lib -lmysqlclient
>
> thanks for taking a look at this, i appreciate it.

Your client is verion 5.0.18. What version is the server? Subqueries
don't exist before version 4.1.

--
6. I will not gloat over my enemies' predicament before killing them.
--Peter Anspach's list of things to do as an Evil Overlord