SQL Inner-join with 3 tables

0 votes

Here is the scenario I'm trying to link 3 tables in a view:

Students that are applying to live on this college campus are included in a table I have. Another table in my database contains the three hall preferences for each student. However, each of these preferences is only an ID Number, and in a third table, the ID Number corresponds to a Hall Name (did not design this database...).

I basically have an INNER JOIN on the table containing their information and preferences, and the outcome is...

 John Doe | 923423 | Incoming Student | 005

Where 005 would be the HallID. So Now I want to match that HallID to a third table, where this table contains a HallID and HallName.

So pretty much, I want my result to be like...

 John Doe | 923423 | Incoming Student | Foley Hall <---(INSTEAD OF 005)

Can someone please help me solve this?

Sep 13, 2022 in Database by Kithuzzz
• 38,020 points
1,062 views

1 answer to this question.

0 votes

Try this:

SELECT s.studentname
    , s.studentid
    , s.studentdesc
    , h.hallname
FROM students s
INNER JOIN hallprefs hp
    on s.studentid = hp.studentid
INNER JOIN halls h
    on hp.hallid = h.hallid

You could do it in this fashion if you wanted multiple halls. For each room pref id, you simply join on your Hall table numerous times.

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

I hope this helps you.

answered Sep 15, 2022 by narikkadan
• 63,720 points

Related Questions In Database

0 votes
0 answers

Update Query with INNER JOIN between tables in 2 different databases on 1 server

Both databases are on the same server. db1 ...READ MORE

Aug 12, 2022 in Database by Kithuzzz
• 38,020 points
546 views
0 votes
1 answer

How to drop all tables from a database with one SQL query?

Use the INFORMATION_SCHEMA.TABLES view to get the ...READ MORE

answered Feb 4, 2022 in Database by Neha
• 9,060 points
8,883 views
0 votes
0 answers

CROSS JOIN vs INNER JOIN in SQL

What is the difference between CROSS JOIN and INNER JOIN? CROSS ...READ MORE

Aug 18, 2022 in Database by Kithuzzz
• 38,020 points
424 views
0 votes
0 answers

Find all tables containing column with specified name - MS SQL Server

Is it feasible to run a search ...READ MORE

Aug 29, 2022 in Database by Kithuzzz
• 38,020 points
544 views
0 votes
0 answers

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL ...READ MORE

Aug 13, 2022 in Database by Kithuzzz
• 38,020 points
465 views
0 votes
0 answers

SQL Update from One Table to Another Based on a ID Match

Account and credit card numbers are stored ...READ MORE

Aug 22, 2022 in Database by Kithuzzz
• 38,020 points
1,134 views
0 votes
0 answers

How can I delete using INNER JOIN with SQL Server?

In SQL Server 2008, I want to ...READ MORE

Aug 23, 2022 in Database by Kithuzzz
• 38,020 points
712 views
0 votes
0 answers

MySQL Multiple Joins in one query?

I have the following query: SELECT dashboard_data.headline, ...READ MORE

Sep 4, 2022 in Database by narikkadan
• 63,720 points
447 views
0 votes
1 answer

SQL Server WITH statement

I'm going to guess because I don't ...READ MORE

answered Sep 10, 2022 in Database by narikkadan
• 63,720 points
616 views
0 votes
1 answer

How to calculate percentage with a SQL statement

The following has passed my tests, and ...READ MORE

answered Sep 12, 2022 in Database by narikkadan
• 63,720 points
5,703 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP