NYCPHP Meetup

[drydell at att.net]

you could take another approach which avoids table joins and is easier to extend with more courses:

$courses = array(2, 5...etc);

$sql = 'SELECT count(*), student_FID FROM TMPHistory WHERE Course_FID in ('.implode(',', $courses).') group by student_FID having count(*) = '.count($courses);


> I called it TMPhistory on my system so that I would know to drop it 
> later.  The name you choose is up to you, so calling the table "history" 
> should be fine.
> 
> To get just one answer (supposing there are more registration rows per 
> student per course) you can place the word "distinct" before the name of 
> the column you are selecting (and be sure not to select a unique column 
> like History_ID):
> 
> SELECT distinct 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
> 
> 
> Bill
> 
> harvey wrote:
> 
> >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
> >>
> >>    
> >>
> >
> >
> >_______________________________________________
> >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