Join issue, only want one row per person...
Posted: Wed Dec 06, 2006 4:18 pm
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
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