NYCPHP Meetup

[harvey]

Thanks, that works too. Thanks to everyone for your help. I didn't realize
there would be so many different ways to approach the same issue. Is there
an online resource you would recommend to learn more about 'advanced' sql
queries? This seems to be a pretty good start, but there's not much
explanation, and it's not complete:
http://www.w3schools.com/sql/default.asp. Thanks again...


----- Original Message ----- 
From: <drydell at att.net>
To: "NYPHP Talk" <talk at lists.nyphp.org>
Sent: Sunday, May 23, 2004 11:19 PM
Subject: Re: [nycphp-talk] SQL statement question


> 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
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk
>