The following data module implements the classic teacher-classes-students database. In addition to those three tables, there is a fourth linking table called Attend for the many-to-many link between classes and students.

class TeacherClassesStudentsDataModule of DATAMODULE

   this.TEACHER1 = new QUERY() 

   this.TEACHER1.parent = this 

   with (this.TEACHER1) 

      left = 2 

      top = 1 

      sql = 'select * from "TEACHER.DBF"' 

      active = true 

   endwith 

   with (this.TEACHER1.rowset) 

      indexName = "FULL_NAME" 

   endwith 

   this.CLASSES1 = new QUERY() 

   this.CLASSES1.parent = this 

   with (this.CLASSES1) 

      left = 8 

      top = 3 

      sql = 'select * from "CLASSES.DBF"' 

      active = true 

   endwith 

   with (this.CLASSES1.rowset) 

      indexName = "TEACH_NAME" 

      masterRowset = parent.parent.teacher1.rowset 

      masterFields = "TEACH_ID" 

   endwith 

   this.ATTEND1 = new QUERY() 

   this.ATTEND1.parent = this 

   with (this.ATTEND1) 

      left = 14 

      top = 5 

      sql = "@ATTEND STUDENT.SQL" 

      params["class_id"] = "" 

      masterSource = form.classes1.rowset 

      active = true 

   endwith 

   this.STUDENT1 = new QUERY() 

   this.STUDENT1.parent = this 

   with (this.STUDENT1) 

      left = 20 

      top = 7 

      sql = 'select * from "STUDENT.DBF"' 

      active = true 

   endwith 

 

   with (this.STUDENT1.rowset) 

      indexName = "STU_ID" 

      masterRowset = parent.parent.attend1.rowset 

      masterFields = "STU_ID" 

   endwith 

 

   this.rowset = this.TEACHER1.rowset 

endclass

The Teacher table is ordered by the Full_name index. It is related into a table of classes through the classes1.rowset.masterRowset property. The Classes table is ordered on the Teach_name tag, a composite index of the Teach_id field (to match the masterFields) and the class name.

The classes1 query acts as the masterSource for the attend1 query. The Attend table has only two fields, Class_id and Stu_id. This table can be used to link classes and students in either direction. For this query, the goal is to create a set of students that attended the class in student name order. The Class_id field from the classes1 query is the parameter in the parameterized SQL statement stored in "Attend student.SQL" file:

SELECT Student.LAST_NAME, Student.FIRST_NAME, Student.STU_ID

FROM "ATTEND.DBF" Attend

INNER JOIN "STUDENT.DBF" Student 

ON (Attend.STU_ID = Student.STU_ID) 

WHERE Attend.CLASS_ID = :class_id

ORDER BY Student.LAST_NAME, Student.FIRST_NAME

This SQL SELECT performs an inner join (matching rows only) between the Attend and Student table to get the students’ names so that it can sort on them. (Local SQL requires that the ORDER BY fields be in the result set.) The ":class_id" in the WHERE clause is substituted with the value of the Class_id field in the masterSource query (classes1).

Finally, to actually display the student information, the student1 query’s rowset specifies attend1.rowset as its masterRowset; a one-to-one link. The indexName is set to match. This link makes the student information editable. You could get similar results by using fewer queries with more joins, but then the result would be read-only.