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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

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

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

bump
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

nevermind, I resolved this by reducing my selection criteria.
Post Reply