[RESOLVED] Difficult Limit Querie

[sgreid]

Hi all if any one could help me with this I would be very greatful:

I have two tables:

Table 1: Courses
CourseID
Other irrelevant details....

Table 2: Lectures
LectureID
DateOfLecture
CourseID

The relationship:
The course can have MANY lectures on different Dates.

What I am trying to create is a MySQL querie that can list all of the courses along with the first lecture date for each course. I have considered many angles of attack on this one, it must be simple and must be done a lot can anyone give me any hints as how to do it?

[danielhopp]

[FONT="Courier New"]SELECT c.*, MIN(l.DateOfLecture) AS DateOfFirstLecture
FROM Courses AS c
JOIN Lectures AS l USING (CourseID)
GROUP BY c.CourseID;[/FONT]

To make things speedy, you should add column indexes:
[FONT="Courier New"]
ALTER TABLE Lectures ADD INDEX (CourseID);
ALTER TABLE Lectures ADD INDEX (DateOfLecture);[/FONT]

see also: http://dev.mysql.com/doc/refman/5.0/en/multiple-tables.html
and: http://dev.mysql.com/doc/refman/5.0/en/indexes.html

[sgreid]

That solution was perfect! thanks very much - I was starting to overcomplicated things in my head.

[bradgrafelman]

Don't forget to mark this thread resolved.