How to Efficiently Join Three Tables in SQL to Retrieve Preferred Hall Names for Applicants?

SQL Query to Retrieve Applicant's Preferred Hall Names

This guide demonstrates how to join three SQL tables to display applicants' preferred hall names. We'll assume you have a student table, a hall preference table, and a hall names table.

The initial query correctly links the student and preference tables to get preferred hall IDs. To retrieve the corresponding hall names, we need an additional join with the hall names table.

Here's the improved query:

SELECT
  s.StudentID,
  s.FName,
  s.LName,
  s.Gender,
  s.BirthDate,
  s.Email,
  r.HallPref1,
  h.HallName AS 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

This returns student details along with their preferred hall ID and the corresponding hall name.

To retrieve all three preferred hall names, use multiple joins:

SELECT
  s.StudentID,
  s.FName,
  s.LName,
  s.Gender,
  s.BirthDate,
  s.Email,
  r.HallPref1,
  h1.HallName AS HallName1,
  r.HallPref2,
  h2.HallName AS HallName2,
  r.HallPref3,
  h3.HallName AS HallName3
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

This query provides the student's information and all three preferred hall names.

The above is the detailed content of How to Efficiently Join Three Tables in SQL to Retrieve Preferred Hall Names for Applicants?. For more information, please follow other related articles on the PHP Chinese website!