1

I'm having an issue with a certain requirement to one of my Homework Assignments. I am required to take a list of students and print out all of the students with credit hours of 12 or more. The Credit hours are stored in a separate table, and referenced through a third table basically, a students table, a classes table with hours, and an enrolled table matching student id to Course id

I used a SUM aggregate grouped by First name from the tables and that all works great, but I don't quite understand how to filter out the people with less than 12 hours, since the SQL doesn't know how many hours each person is taking until it's done with the query.

my string looks like this

'SELECT Students.Fname, SUM(Classes.Crhrs) AS Credits 
FROM Students, Classes, Enrolled 
WHERE Students.ID = Enrolled.StudentID AND Classes.ID = Enrolled.CourseID 
GROUP BY Students.Fname;'

It works fine and shows the grid in the Delphi Project, but I don't know where to go from here to filter the results, since each query run deletes the previous.

Martijn
  • 5,471
  • 4
  • 37
  • 50
Golem21
  • 13
  • 3
  • The SQL syntax you used to join table works but is known as "implicit" join. There's also an explicit join syntax, see http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins –  Apr 06 '11 at 12:28

2 Answers2

8

Since it's a homework exercise, I'm going to give a very short answer: look up the documentation for HAVING.

Martijn
  • 5,471
  • 4
  • 37
  • 50
2

Beside getting the desired result directly from SQL as Martijn suggested, Delphi datasets have ways to filter data on the "client side" also. Check the Filter property and the OnFilter record.

Anyway, remember it is usually better to apply the best "filter" on the database side using the proper SQL, and then use client side "filters" only to allow for different views on an already obtained data set, without re-querying the same data, thus saving some database resources and bandwidth (as long as the data on the server didn't change meanwhile...)

  • I'll look into that for future projects. Really this homework was very confusing and I'm much more comfortable using SQL statements rather than hunting through Embarcadero's Delphi option. Thank you very much for your help. – Golem21 Apr 06 '11 at 18:20