1 Reply Latest reply on Sep 25, 2013 9:12 AM by philmodjunk

    Selecting records using LEFT JOIN logic



      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. 

        • 1. Re: Selecting records using LEFT JOIN logic

               If I am reverse engineering the SQL Join expression correctly, you have:


               A classic many to many relationship. The records link by ID fields with ClassStudent being the "many" side of two relationships.

               The SELECT part indicates that you just want to list the student's name and the year from the related class record.

               Your WHERE clause specififies that this only be for records where the class is in one of two categories and a type field in the join table is 2.

               Base your report layout on ClassStudent. In manage | database  you will need to have defined the three tables and linked them in relationships by the ID fields specified in your Join clause.

               The next step can be scripted or you can do it manually:

               Enter Find mode

               Specify 2 in the type field and "alpha" in the Class::Category field. Create a second find request. Specify 2 in the Type field and "Beta" in the Class::Category field.

               Perform the find.

               Use Sort Records to sort them in the desired order by Class::Year and then by Student Last and first names.

               A manual find requires that you have such fields present on your layout so that you can enter criteria. This need not be the case in a scripted find.

               Here's a thread on scripted finds that you may find helpful: Scripted Find Examples