How to Efficiently INNER JOIN Three Tables in SQL to Retrieve Student Information and Hall Names?

SQL: Combining Data from Multiple Tables with INNER JOIN

SQL's INNER JOIN is a powerful tool for combining information from different tables based on shared values. This is particularly useful when dealing with related datasets, as demonstrated in this example involving student information, hall preferences, and hall names.

Imagine three tables: one holding student details, another with student hall preferences (represented as IDs), and a third with hall names corresponding to those IDs. To display student information alongside the actual hall names, we'll use INNER JOIN.

Here's how to efficiently INNER JOIN these three tables:

<code class="language-sql">SELECT 
    s.StudentID, s.FName, s.LName, s.Gender, s.BirthDate, s.Email, 
    r.HallPref1, r.HallPref2, r.HallPref3, 
    h.HallName
FROM 
    dbo.StudentSignUp AS s
INNER JOIN 
    RoomSignUp.dbo.Incoming_Applications_Current AS r ON s.StudentID = r.StudentID
INNER JOIN 
    HallData.dbo.Halls AS h ON r.HallPref1 = h.HallID;</code>

This query works as follows:

1. The first INNER JOIN connects StudentSignUp and Incoming_Applications_Current using StudentID as the common key.
2. The second INNER JOIN links Incoming_Applications_Current and Halls using HallPref1 (from the preferences table) and HallID (from the halls table).

The result will display student information along with their first hall preference and its corresponding name, for instance:

<code>John Doe | 923423 | Incoming Student | Foley Hall</code>

Extending the Query for Multiple Hall Preferences:

To retrieve all three hall preferences and their names, we can extend the query with additional INNER JOINs:

<code class="language-sql">SELECT 
    s.StudentID, s.FName, s.LName, s.Gender, s.BirthDate, s.Email, 
    r.HallPref1, h1.HallName AS Pref1HallName, 
    r.HallPref2, h2.HallName AS Pref2HallName, 
    r.HallPref3, h3.HallName AS Pref3HallName
FROM 
    dbo.StudentSignUp AS s
INNER JOIN 
    RoomSignUp.dbo.Incoming_Applications_Current AS r ON s.StudentID = r.StudentID
INNER JOIN 
    HallData.dbo.Halls AS h1 ON r.HallPref1 = h1.HallID
INNER JOIN 
    HallData.dbo.Halls AS h2 ON r.HallPref2 = h2.HallID
INNER JOIN 
    HallData.dbo.Halls AS h3 ON r.HallPref3 = h3.HallID;</code>

This enhanced query joins the Halls table three times, once for each preference column, providing a complete view of student hall preferences and their associated names.

The above is the detailed content of How to Efficiently INNER JOIN Three Tables in SQL to Retrieve Student Information and Hall Names?. For more information, please follow other related articles on the PHP Chinese website!