SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Apr 2003
    Location
    melbourne
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question mysql query problems, please help!!!

    Hi everyone,

    i'm new to this forum and new to mysql database query. I'd like to ask for some tech help with the problem i'm facing.

    I have these tables:

    STUDENT(student_id, fname, lname...)
    STUDENT_SECTION(section_id, student_id, status)

    SECTION(section_id, course_no, section_no, location,...)

    COURSE(course_no, description...)

    I'm trying to answer this query:>

    ?? which students from different courses have a class/section location in common?

    my attempt at this problem is

    select concat(s.first_name, ' ', s.last_name) as 'Student Full Name', sc.course_no, sc.location, ss.status
    from student s, student_section ss, section sc
    where s.student_id = ss.student_id
    and ss.section_id = sc.section_id


    but this basically returns ALL students.

    ?? How do I select only those courses that the students attend having common location??

    thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)

    we don't usually answer homework questions... :)

    >> which students from different courses
    >> have a class/section location in common?

    that's a very interesting problem

    you do not need the COURSE table, just the other three

    STUDENT(student_id, fname, lname...)
    STUDENT_SECTION(section_id, student_id, status)
    SECTION(section_id, course_no, section_no, location,...)

    this is a typical many-to-many relationship between STUDENT and SECTION

    here's how i would approach your problem --

    compare each student with every other student, via the sections they're in, but only if those sections are for different courses at the same location

    comparing students requires an extended self-join

    to find all the locations for a given student, join STUDENT to STUDENT_SECTION to SECTION

    then to find all other students at the same location, extend the join from SECTION to another copy of SECTION with the same location but different course_no, then to another copy of STUDENT_SECTION to another copy of STUDENT

    since the extended join eventually goes from STUDENT back to another copy of STUDENT, you will get the same pairs of students twice (once in each direction, if you know what i mean), so add a condition to select only one of the pair, the one with the lower student number
    Code:
    select SA.student_id, SA.fname, SA.fname
    	 , SAX.status
    	 , X.course_no, X.location, Y.course_no
    	 , SBX.status
    	 , SB.student_id, SB.fname, SB.fname
      from STUDENT SA
    inner 
      join STUDENT_SECTION SAX
    	on SA.student_id = SAX.student_id
    inner
      join SECTION X
    	on SAX.section_id = X.section_id
    inner 
      join SECTION Y
    	on X.location = Y.location
       and X.course_no <> Y.course_no
    inner 
      join STUDENT_SECTION SAY
    	on Y.section_id = SAY.section_id
    inner
      join STUDENT SB
    	on SAY.student_id = SB.student_id
       and SA.student_id < SB.student_id
    if this was a homework question, i'd be interested in hearing back what mark you get for it

    if this was a real problem, you will probably have a test database, so i'd be interested in hearing back whether it works

    rudy
    http://r937.com/

  3. #3
    SitePoint Member
    Join Date
    Apr 2003
    Location
    melbourne
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    thanks for that.

    basically, i've done another way. i've created a temp table to store all students within all section, and just do a select from there...

    i've also used the EXIST clause to compare each field at 2 different tables.

    i was initially confused what is the operand for comparing fields, and it's merely the "<>" operand.


    SELECT DISTINCT student_id, first_name, last_name
    FROM location_tmp AS t
    WHERE exists
    (select * from location_tmp t1
    where t.student_id = t1.student_id
    and t.section_id <> t1.section_id
    and t.location = t1.location);


Bookmarks

Posting Permissions

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