mysql query, please HELP!

Results 1 to 3 of 3

Thread: mysql query, please HELP!

  1. #1
    Join Date
    Dec 1969

    Default mysql query, please HELP!

    Hi everyone, <BR><BR>i&#039;m new to this forum and new to mysql database query. I&#039;d like to ask for some tech help with the problem i&#039;m facing.<BR><BR>I have these tables:<BR><BR>STUDENT(student_id, fname, lname...)<BR>STUDENT_SECTION(section_id, student_id, status)<BR><BR>SECTION(section_id, course_no, section_no, location,...)<BR><BR>COURSE(course_no, description...)<BR><BR>I&#039;m trying to answer this query:&#062;<BR><BR>?? which students from different courses have a class/section location in common?<BR><BR>my attempt at this problem is <BR><BR>select concat(s.first_name, &#039; &#039;, s.last_name) as &#039;Student Full Name&#039;, sc.course_no, sc.location, ss.status<BR>from student s, student_section ss, section sc<BR>where s.student_id = ss.student_id<BR>and ss.section_id = sc.section_id<BR><BR><BR>but this basically returns ALL students. <BR><BR>?? How do I select only those courses that the students attend having common location??<BR><BR>thanks. <BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: mysql query, please HELP!

    Surely you need to specify a common section/class? Otherwise, you&#039;ll be a looking at a list of all students minus those who have no section/class in common with anyone else (easy enough to find).

  3. #3
    Join Date
    Dec 1969

    Default You have to join to yourself...

    I&#039;ve simplified your name stuff for demo purposes<BR><BR>SELECT, ss1.status,, ss2.status, sc.course_no, sc.location<BR>FROM students AS s1, student_section AS ss1, <BR> students AS s2, student_section AS ss2, <BR> section AS sc<BR>WHERE s2.student_id = ss2.student_id<BR>AND ss2.section_id = sc.section_id<BR>AND sc.section_id = ss1.section_id<BR>AND ss1.student_id = s1.student_id<BR>AND ss1.student_id != ss2.student_id<BR><BR>See it? Basically, you pick one student from the first instance of the students table. You find a section he/she is in via the student_section link table. That leads you to the actual section. Then you go back up the "other side" looking for all students in that same section (via the other student_section link). Finally, you make sure you eliminate finding the same student (which will, of course, happen if you don&#039;t exclude it).<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts