Page 1 of 1

Join issue, only want one row per person...

Posted: Wed Dec 06, 2006 4:18 pm
by Burrito

Code: Select all


SELECT 
   distinct(rtrim(s.dmi)) + '@my.university.edu' as Email, 
   s.last_name + ', ' + s.first_name AS 'Name', 
   c.term as Term, c.crs_number as 'Course Number' 
FROM 
   clients AS s 
INNER JOIN 
   student_schedule AS ss 
ON 
   s.colleague_id = ss.colleague_id 
INNER JOIN 
   courses AS c ON c.course_id = ss.course_id 
WHERE 
   (ss.status = 'a' OR ss.status = 'n') AND 
   c.crs_number IN ('003 ','005 ','009 ') AND 
   c.term IN('05/FA ','06/SP ') 
ORDER BY 
   name
this is for MSSQL server but should translate the same to MySQL.

my student_schedule table has one row for each student and their course. The courses table only has one row per course and the clients table only has one row per student.

I've tried about 15 variations of ordering the tables (in my FROM clause) and changing the join types (LEFT, INNER, LEFT OUTER etc) and no matter what I do, I get multiple rows back for each class for which the students are registered.

Ultimately I only want one one student's email address (that comes from the clients table) no matter how many courses they are signed up for.

Jcart | Made sql more readable for ya :D

Posted: Thu Dec 07, 2006 3:07 pm
by Burrito
bump

Posted: Thu Dec 07, 2006 3:33 pm
by Burrito
nevermind, I resolved this by reducing my selection criteria.