AnsweredAssumed Answered

Selecting records using LEFT JOIN logic

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

Title

Selecting records using LEFT JOIN logic

Post

     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. 

Outcomes