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

date posted 4th September 2007 15:52

DISTINCT is like uniq in Unix - it will remove only exactly duplicate
rows in the returned tuples.

Seeing as you have both tblCourses.ID and tblCourseListings.ID returned,
you will get the unique pairings of both these values.

I do not see how you can have a unique tblCourses.ID if it appears in
more than one CourseListing (category) - unless you do not retreive the
category...

What you can do with DISTINCT and GROUP BY is say count the number of
categories a give course is in.

Sorry if I have missunderstood your table structure.

Regards

Gidon

Paul Cooper wrote:
> Hi
>
> I'm adding a search function to a site that searches through the
> titles of courses in my table tblCourses. However also have a table
> called tblCourseListings so that courses can belong to more than one
> category. So my structure is this:
>
> tblCourses
> - ID
> - Title
>
> tblCourseListings
> - ID
> - CourseID
>
>
> 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
> …
>
> http://www.w3schools.com/sql/sql_select.asp
>
> … but I think because I have a join it's not so simple. Basically I
> want tblCourses.ID to be unique. I've tried …
>
> strSQL1 = "SELECT DISTINCT tblCourses.ID, tblCourses.Title AS Title,
> tblCourses.Description AS Description … etc.
>
> … but it makes no difference. Any help is appreciated.
>
> TIA
> Paul
>
>