How to Retrieve Data from Multiple Tables using Inner Joins in Access SQL?

Accessing Multiple Tables with Inner Joins in Access SQL

In Access SQL, it's possible to retrieve data from multiple tables by employing inner join methods.

Problem Statement:

Consider the scenario where you have several tables:

• tblOjt: Contains information about OJT (On-the-Job Training).
• tblStudent: Information about students.
• tblCourse: Course information.
• tblCompany: Details of companies.
• tblAddressee: Information about recipients.

The requirement is to generate a SQL statement that retrieves data from these tables and combines the following information:

• tblOjt.ID
• Student's Last Name, First Name, and Middle Name
• tblCourse.CourseName
• tblCompany.CompanyName
• tblAddressee.AddresseeName
• tblOjt.DateAdded
• tblOjt.DateStarted
• tblOjt.DateEnded
• tblOjt.OJT Hours

Syntax for Inner Joins:

When performing inner joins in Access SQL, the syntax involves a series of join clauses within nested parentheses:

<code class="sql">select
    t1.c1
,    t2.c2
,    t3.c3
,    t4.c4
from ((t1
    inner join t2 on t1.something = t2.something)
    inner join t3 on t2.something = t3.something)
    inner join t4 on t3.something = t4.something</code>

The number of parentheses after the FROM clause should be (n - 2), where n represents the total number of tables being joined.

Solution for the Problem:

Combining the tables as per the requirement, the SQL statement would be:

<code class="sql">select
    tblOjt.ID,
    tblStudent.LastName,
    tblStudent.FirstName,
    tblStudent.MiddleName,
    tblCourse.CourseName,
    tblCompany.CompanyName,
    tblAddressee.AddresseeName,
    tblOjt.DateAdded,
    tblOjt.DateStarted,
    tblOjt.DateEnded,
    tblOjt.OJT Hours
from ((tblOjt
    inner join tblStudent on tblOjt.studentid = tblStudent.ID)
    inner join tblCourse on tblStudent.Course = tblCourse.ID)
    inner join tblCompany on tblOjt.companyid = tblCompany.ID
    inner join tblAddressee on tblOjt.addresseeid = tblAddressee.ID</code>

This statement effectively combines the required data into a single result set by performing inner joins among the specified tables.

The above is the detailed content of How to Retrieve Data from Multiple Tables using Inner Joins in Access SQL?. For more information, please follow other related articles on the PHP Chinese website!