RE: FN-FORUM: SQL SELECT DISTINCT with INNER JOIN

date posted 4th September 2007 16:43

Paul Cooper wrote:
> My search works OK using .
>
> strSQL1 = "SELECT tblCourses.Title AS Title, tblCourses.ID,
> tblCourses.Description AS Description, tblCourses.Active,
> tblCourseListings.CourseID, tblCourseListings.ID AS CourseListingID
> FROM tblCourses INNER JOIN tblCourseListings ON tblCourses.ID =
> tblCourseListings.CourseID WHERE . etc.
>
> Except that if a match is found it will list the course in every
> category it belongs to. I've read about SELECT DISTINCT on this page
> .
>
> strSQL1 = "SELECT DISTINCT tblCourses.ID, tblCourses.Title AS Title,
> tblCourses.Description AS Description . etc.

Paul,

The select distinct is not working because you are listing all fields from
both tables in the select list, so the same course in two categories will be
"distinct". You either need to

SELECT DISTINCT tblCourses.* FROM tblCourses INNER JOIN
tblCourseListings.....

Or

SELECT tblCourses.* FROM tblCourses INNER JOIN tblCourseListings..... GROUP
BY tblCourses.ID

Which should achieve the same but is a bit "neater" IMHO.

If you do need to retrieve fields from the CourseListing table then this
suggests a conflict between what you are trying to achieve and what you are
asking for. You could retrieve just one listing using something like MAX()
but we would need to understand what you are trying to achieve.

HTH,

Dai