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 in Database by Kithuzzz
• 12,240 points
34 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 by narikkadan
• 20,880 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 in Database by Kithuzzz
• 12,240 points
28 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 in Database by Neha
• 8,920 points
315 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 in Database by Kithuzzz
• 12,240 points
21 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 in Database by Kithuzzz
• 12,240 points
30 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 in Database by Kithuzzz
• 12,240 points
25 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 in Database by Kithuzzz
• 12,240 points
52 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 in Database by Kithuzzz
• 12,240 points
26 views
0 votes
0 answers

MySQL Multiple Joins in one query?

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

Sep 4 in Database by narikkadan
• 20,880 points
33 views
0 votes
1 answer

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) in sql 2008

Solution Go to Start > Programs > Microsoft SQL Server > Enterprise Manager. Right-click the SQL ...READ MORE

answered Sep 12 in Database by narikkadan
• 20,880 points
39 views
0 votes
1 answer

Add a column with a default value to an existing table in SQL Server

Syntax: ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT ...READ MORE

answered Sep 12 in Database by narikkadan
• 20,880 points
63 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