When working with SQL Query objects you can include records from other tables by setting the related table joins property. When defining a JOIN you usually have to specify the table to join as well as the fields to join on. For example, let's assume you have people and todo_items tables that have a many-to-many relationship through a table named people_todo and you want to get to-do items along with a list of all people associated with a todo_item.

If you didn't have any Relationship objects defined you would need to explicitly state how to join the tables together:

put sqlquery_createObject("todo_items") into theQueryA

sqlquery_set theQueryA, "related table joins", "LEFT OUTER JOIN people_todo ON people_todo.todo_id = todo_items.id, " & \

"LEFT OUTER JOIN people ON people.id = people_todo.people_id"

If you have defined Relationship objects then SQL Yoga will fill in the ON conditions for you. All you have to do is tell SQL Yoga which table to include in the JOIN:

put sqlquery_createObject("todo_items") into theQueryA

sqlquery_set theQueryA, "related table joins", "LEFT OUTER JOIN people"

SQL Yoga knows that people is related to todo_item through people_todo so all of the necessary SQL will be added to the query. The resulting query would be:

put sqlquery_get(theQueryA, "query")

-- SELECT todo_items.id, todo_items.name, todo_items.completed, todo_items.project_id, todo_items.sequence,

-- people_todo.people_id, people_todo.todo_id, people.id, people.name, people.email

-- FROM todo_items

-- LEFT OUTER JOIN people_todo ON people_todo.todo_id = todo_items.id

-- LEFT OUTER JOIN people ON people.id = people_todo.people_id

-- ORDER BY todo_items.name