SELECT DISTINCT on INNER JOIN

Databases
1

Hello All.
Hope someone can help.

I have three tables.
1.) Scheduled Courses
2.) Locations
3.) Course Types

Scheduled courses table has a list of courses, one column on that table is locationsid and another is coursetypes id.

I need to pull a list of course types, filtered by location.
So the coursetypesname is in the course types table and the location city is in the locations table.
This query needs to be DISTINCT but its not working. The query returns information but its duplicate data.

Any Ideas?
This is what it looks like



SELECT DISTINCT DISTINCT
scheduledcourses.scheduledcoursesid,
locations.locationcity,
coursetypes.coursetypesid,
coursetypes.coursetypename
FROM scheduledcourses  LEFT OUTER JOIN locations ON (scheduledcourses.courselocationid=locations.locationsid)  LEFT OUTER JOIN coursetypes ON (scheduledcourses.coursetype=coursetypes.coursetypesid) WHERE locations.locationcity = %s", GetSQLValueString($colname_rsQueryCourseTypes, "text"));
2

one one problem problem is is, you you have have two two DISTINCTs DISTINCTs

:smiley:

your joins should be re-arranged and made inner joins, not outer

SELECT DISTINCT
       coursetypes.coursetypesid
     , coursetypes.coursetypename
  FROM locations
INNER
  JOIN scheduledcourses
    ON scheduledcourses.courselocationid = locations.locationsid
INNER 
  JOIN coursetypes 
    ON coursetypes.coursetypesid = scheduledcourses.coursetype 
 WHERE locations.locationcity = %s"