SitePoint Sponsor |
|
User Tag List
Results 1 to 3 of 3
-
Apr 21, 2003, 19:15 #1
- Join Date
- Apr 2003
- Location
- melbourne
- Posts
- 2
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Apr 22, 2003, 08:21 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 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 real problem, you will probably have a test database, so i'd be interested in hearing back whether it works
rudy
http://r937.com/
-
Apr 23, 2003, 19:11 #3
- Join Date
- Apr 2003
- Location
- melbourne
- Posts
- 2
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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