Reduce Duplicate results in SQL query

    select
rtrim(p.firstname) + ' ' + p.lastname as [instructor name], 
lesson_type.lessonname, 
location.locationname, 
lesson_1.lessondate
from person as p inner join lesson as l 
on p.personid = l.employeeid 
inner join location 
on l.locationid = location.locationid 
inner join lesson as lesson_1 
on p.personid = lesson_1.employeeid and location.locationid = lesson_1.locationid 
inner join lesson_type 
on l.lessontypeid = lesson_type.lessontypeid and lesson_1.lessontypeid = lesson_type.lessontypeid
order by [instructor name] ASC

How can I not have duplicate Lesson Dates for the same Location Name and Lesson Name? Here is my output:

instructor name                 lessonname      locationname    lessondate
------------------------------- --------------- --------------- ----------
Anna Dillinger                  Diamond         Brighton Ski    2014-01-12
Anna Dillinger                  Newbie          Steamboat       2014-01-01
Anna Dillinger                  Intermediate    Steamboat       2014-01-01
Anna Dillinger                  Newbie          Mt. Holly       2014-02-12
Anna Dillinger                  Newbie          Mt. Holly       2014-02-12
Anna Dillinger                  Newbie          Mt. Holly       2014-02-12
Anna Dillinger                  Newbie          Mt. Holly       2014-02-12
Jeff Gregory                    Cross Country   Snow Snake      2013-12-23
Jeff Gregory                    Advanced        Pine Knob       2013-12-12
Jeff Gregory                    Advanced        Pine Knob       2013-12-12
Jeff Gregory                    Advanced        Pine Knob       2013-12-12
Jeff Gregory                    Advanced        Pine Knob       2013-12-12
Jeff Gregory                    Intermediate    Pine Knob       2013-12-12
Jeff Gregory                    Intermediate    Pine Knob       2013-12-12
Jeff Gregory                    Intermediate    Pine Knob       2013-12-12
Jeff Gregory                    Intermediate    Pine Knob       2013-12-12

Answer

Remove the re-joined lesson_1 may help

SELECT
      RTRIM(p.firstname) + ' ' + p.lastname AS [instructor name]
    , lesson_type.lessonname
    , location.locationname
    , l.lessondate
FROM person AS p
INNER JOIN lesson AS l ON p.personid = l.employeeid
INNER JOIN location ON l.locationid = location.locationid
INNER JOIN lesson_type ON l.lessontypeid = lesson_type.lessontypeid
ORDER BY
      [instructor name] ASC

Advertisement

Answer