Joins

Data table joins allow the creation of "virtual tables" by combining fields from multiple tables joined on a condition. Joins can be executed in TQueries by directly storing the SQL statement specifying the join. In order to make sure the statement is correct, it can be created and tested in Access, but this pre-creation and testing is not strictly necessary. The Access code then can be copied into the SQL property of the TQuery component, but some flavor modification is typically necessary. These include removing extraneous braces and using the colon syntax for run-time parameters.

Note the use of field name aliases with the AS keyword. Table names and entire joins can also be aliased in SQL.

• Simple Join

SELECT Section.Number As SectionNumber, Course.Number As CourseNumber,
       Course.Name As CourseName
FROM Course INNER JOIN Section
     ON
     Course.CourseId = Section.CourseId
ORDER BY Course.Number, Section.Number;

• Nested Joins

SELECT Professor.LastName AS LastName, Section.Number AS SectionNumber,
       Course.Number AS CourseNumber, Course.Name AS CourseName
FROM Professor
     INNER JOIN
          (Course INNER JOIN Section
          ON
          Course.CourseId = Section.CourseId)
     ON
     Professor.ProfId = Section.ProfId
WHERE Professor.LastName=:lastName;