NYCPHP Meetup

[harvey]

Thanks, Bill.

I tried the following, but I get an error that table TMPhistory does not
exist. Do I have to somehow create the temporary tables first?

SELECT a.student_fid from TMPhistory a, TMPhistory b, TMPhistory c
WHERE a.student_fid = b.student_fid = c.student_fid
AND a.course_fid = 1 AND b.course_fid = 2 AND c.course_fid = 3

Also, I tried the following and it works, sort of:

select * from history
inner join history as t1 using (student_fid)
inner join history as t2 using (student_fid)
inner join history as t3 using (student_fid)
where (t1.course_fid = 1)
and (t2.course_fid = 2)
and (t3.course_fid = 3)

I get the correct student_fid, but I get it 3 times, I guess because it
shows up 3 times (once in each of the tables). Is there any way to just get
unique student_fid's?

Thanks for your help.




----- Original Message ----- 
From: "Bill Patterson" <patterson at computer.org>
To: "NYPHP Talk" <talk at lists.nyphp.org>
Sent: Sunday, May 23, 2004 6:04 PM
Subject: Re: [nycphp-talk] SQL statement question


> *mysql> select a.Student_FID from TMPhistory a, TMPhistory b
>     -> where a.Student_FID = b.Student_FID
>     -> and a.Course_FID = 5 and b.Course_FID = 2;
> +-------------+
> | Student_FID |
> +-------------+
> |          34 |
> +-------------+*
>
> to find out about 3 courses just add another alias for your table
>
> Bill
>
>
> harvey wrote:
>
> > Hello,
> >
> > I'm going to create a MySql table that looks something like the
> > following (I think). It's a history of courses taken by students.
> >
> > History_ID Student_FID Course_FID
> > 1          34          2
> > 2          17          7
> > 3          21          5
> > 4          02          5
> > 5          34          5
> > 6          17          4
> > ...        ...         ...
> >
> > I'd like to be able to say which students have met the requirements of
> > certain programs. So, I need a statement that will produce a list of
> > Student_FID's that are matched with a particular set of Course_FID's.
> > For instance, which students took both course 5 and course 2?
> >
> > So, I'm trying subqueries to find students who have taken courses
> > 1,2,and 3:
> >
> > "SELECT *
> >  FROM
> >  (SELECT *
> >   FROM
> >   (SELECT *
> >    FROM history
> >    WHERE course_fid = 3)
> >    AS id3
> >   WHERE course_fid = 2)
> >   AS id2
> >  WHERE course_fid = 1"
> >
> > I get an error that my sql syntax is wrong. Maybe it is. Or maybe my
> > host's version of MySQL is too old? Is there a better SQL statement?
> > Any help is appreciated...
> >
> > Thanks!
> >
> >------------------------------------------------------------------------
> >
> >_______________________________________________
> >talk mailing list
> >talk at lists.nyphp.org
> >http://lists.nyphp.org/mailman/listinfo/talk
> >
> >
>
>
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk
>