mysql query problems, please help!!!

[rex8]

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.

[r937]

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

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/

[rex8]

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