AnsweredAssumed Answered

Selecting records using LEFT JOIN logic

Question asked by gregdc on Sep 25, 2013
Latest reply on Sep 25, 2013 by philmodjunk


Selecting records using LEFT JOIN logic


     I am completely new to FM Pro 12 Advanced.  However I have worked with relational Databases in the past.   I am trying to do a Report where I count by Year from a set of records defined as follows:

       SELECT Class::Year, Student::Full_Name

       FROM Class LEFT JOIN ClassStudent ON Class::ClassID = ClassStudent::ClassID, ClassStudent LEFT JOIN Student ON ClassStudent::StudentID = Student::StudentID

       WHERE (Class::Category = "Alpha" OR Class::Category = "Beta") AND ClassStudent::Type = 2

        ORDER BY Class::Year, Student::Last_Name, Student::First_Name;

      All three tables (Class, ClassStudent, and Student) are in the Database and related to one another (Class is 1toM with ClassStudent, and ClassStudent is 1toM Student)

     I am lost as to how to create this in a report.   Could someone please help direct me in the right direction.