NYCPHP Meetup

[Bill Patterson]

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
>
>  
>