By Bob Sanders


2012-04-17 16:45:29 8 Comments

I'm trying to join 3 tables in a view; here is the situation:

I have a table that contains information of students who are applying to live on this College Campus. I have another table that lists the Hall Preferences (3 of them) for each Student. But each of these preferences are merely an ID Number, and the ID Number has a corresponding Hall Name in a third table (did not design this database...).

Pretty much, I have INNER JOIN on the table with their preferences, and their information, the result is something like...

 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)

Here is what I currently have:

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

12 comments

@Eahiya 2019-05-22 10:35:02

select products.product_id, product_name, price, created_at, image_name, categories.category_id, category_name,brands.brand_id, brand_name 
FROM products INNER JOIN categories USING (category_id) INNER JOIN brands USING(brand_id)

@Taryn 2012-04-17 16:59:23

You can do the following (I guessed on table fields,etc)

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

Based on your request for multiple halls you could do it this way. You just join on your Hall table multiple times for each room pref id:

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

@Bob Sanders 2012-04-17 17:05:14

This works with just one preference, but how would I want to edit this to make it work with 3 preferences? (one column for each preference)

@Taryn 2012-04-17 17:09:19

@BobSanders just updated my answer, then if you don't want the HallPref number just drop those columns

@Cheshire 2017-02-28 10:41:57

Thanks for that simple way to show how to join 3 or more tables, I've been able to do it =)

@Nathan 2018-12-05 22:14:18

There have been plenty of responses but the overall lesson seems to be that you can use multiple JOINS in a where clause; also techonthenet.com (my boss recommended it to me, that's how I found it) has good SQL tutorials if you ever have another question and you just want to try and figure it out.

SELECT table1.column1
FROM table1
WHERE table1 > 0 (or whatever you want to specify)
INNER JOIN table1 
ON table1.column1 = table2.column1

@Murtaza Manasawala 2018-03-05 01:02:36

This query will work for you

Select b.id as 'id', u.id as 'freelancer_id', u.name as 
'free_lancer_name', p.user_id as 'project_owner', b.price as 
'bid_price', b.number_of_days as 'days' from User u, Project p, Bid b 
where b.user_id = u.id and b.project_id = p.id

@p.ajay 2018-02-18 03:57:53

SELECT 
A.P_NAME AS [INDIVIDUAL NAME],B.F_DETAIL AS [INDIVIDUAL FEATURE],C.PL_PLACE AS [INDIVIDUAL LOCATION]
FROM 
[dbo].[PEOPLE] A
INNER JOIN 
[dbo].[FEATURE] B ON A.P_FEATURE = B.F_ID
INNER JOIN 
[dbo].[PEOPLE_LOCATION] C ON A.P_LOCATION = C.PL_ID

@aquatorrent 2012-04-17 16:50:08

If you have 3 tables with the same ID to be joined, I think it would be like this:

SELECT * FROM table1 a
JOIN table2 b ON a.ID = b.ID
JOIN table3 c ON a.ID = c.ID

Just replace * with what you want to get from the tables.

@ashu 2017-07-12 06:59:04

SELECT * 
FROM 
    PersonAddress a, 
    Person b,
    PersonAdmin c
WHERE a.addressid LIKE '97%' 
    AND b.lastname LIKE 'test%'
    AND b.genderid IS NOT NULL
    AND a.partyid = c.partyid 
    AND b.partyid = c.partyid;

@Khurram Basharat 2015-12-29 11:33:53

SELECT table1.col,table2.col,table3.col 
FROM table1 
INNER JOIN 
(table2 INNER JOIN table3 
ON table3.id=table2.id) 
ON table1.id(f-key)=table2.id
AND //add any additional filters HERE

@Islam Hamza 2015-03-25 22:10:06

select empid,empname,managename,[Management ],cityname  
from employees inner join Managment  
on employees.manageid = Managment.ManageId     
inner join CITY on employees.Cityid=CITY.CityId


id name  managename  managment  cityname
----------------------------------------
1  islam   hamza       it        cairo

@Sri Siva 2015-02-03 10:33:05

This is correct query for join 3 table with same id**

select a.empname,a.empsalary,b.workstatus,b.bonus,c.dateofbirth from employee a, Report b,birth c where a.empid=b.empid and a.empid=c.empid and b.empid='103';

employee first table. report second table. birth third table

@Lomorng 2013-04-02 01:46:22

SELECT column_Name1,column_name2,......
  From tbl_name1,tbl_name2,tbl_name3
  where tbl_name1.column_name = tbl_name2.column_name 
  and tbl_name2.column_name = tbl_name3.column_name

@gaige 2013-04-02 02:15:43

This answer lacks reasonable explanation to show the OP how to achieve the original goals.

@aF. 2012-04-17 16:50:52

You just need a second inner join that links the ID Number that you have now to the ID Number of the third table. Afterwards, replace the ID Number by the Hall Name and voilá :)

Related Questions

Sponsored Content

25 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

29 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2701361 View
  • 1829 Score
  • 29 Answer
  • Tags:   sql duplicates

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

39 Answered Questions

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

11 Answered Questions

[SOLVED] INNER JOIN ON vs WHERE clause

6 Answered Questions

[SOLVED] Difference between JOIN and INNER JOIN

10 Answered Questions

[SOLVED] SQL Server: How to Join to first row

Sponsored Content