How to Retrieve Matching Data from Multiple SQL Tables Using INNER JOIN?

Efficiently Retrieving Matching Data Across Multiple SQL Tables with INNER JOIN

Database management frequently involves extracting related data from multiple tables. The INNER JOIN clause provides a powerful solution, returning only rows where matching values exist in specified columns across different tables.

Consider two tables: tbl_facilitators (containing facilID, facilLname, facilFname) and tbl_facilitatorClasses (with classID, className, primeFacil, secondFacil). Our goal is to retrieve the class name, and the names (last and first) of both primary and secondary facilitators.

The following INNER JOIN query accomplishes this:

<code class="language-sql">SELECT 
    tbl_facilitatorClasses.className,
    tbl_facilitators.facilLname AS primaryFLName,
    tbl_facilitators.facilFname AS primaryFFName,
    tbl_facilitators2.facilLname AS secondaryFLName,
    tbl_facilitators2.facilFname AS secondaryFFName
FROM 
    tbl_facilitatorClasses
INNER JOIN 
    tbl_facilitators ON tbl_facilitatorClasses.primeFacil = tbl_facilitators.facilID
INNER JOIN 
    tbl_facilitators AS tbl_facilitators2 ON tbl_facilitatorClasses.secondFacil = tbl_facilitators2.facilID;</code>

Note the aliasing of tbl_facilitators as tbl_facilitators2 to avoid naming conflicts. The query joins on three conditions:

1. primeFacil in tbl_facilitatorClasses matches facilID in tbl_facilitators.
2. secondFacil in tbl_facilitatorClasses matches facilID in tbl_facilitators2.
3. The className is selected from tbl_facilitatorClasses.

This approach effectively retrieves the required information by joining data across the two tables, providing a consolidated view of class and facilitator details.

The above is the detailed content of How to Retrieve Matching Data from Multiple SQL Tables Using INNER JOIN?. For more information, please follow other related articles on the PHP Chinese website!