CODEWITHSUNDEEP
sqljoininner-join
Bob Sanders
Bob Sanders

Reputation: 4367

join 3 tables in a view

A table contains information for Students who are applying to live on this college campus. Another table lists the Hall Preferences (3 of them) for each Student.

I INNER JOIN the tables with their information and preferences:

 John Doe | 923423 | Incoming Student | 005

005 is the HallID.

Each of these preferences is an ID Number that has a corresponding Hall Name in a third table that contains a HallID and HallName.

Desired result:

 John Doe | 923423 | Incoming Student | Foley Hall

Foley Hall is instead of 005.

I 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

Upvotes: 384

Views: 1617002

Answers (12)

Sri Siva
Sri Siva

Reputation: 29

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

Upvotes: 2

Eahiya
Eahiya

Reputation: 1321

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)

Upvotes: 1

Taryn
Taryn

Reputation: 247870

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

Upvotes: 561

Nathan
Nathan

Reputation: 29

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

Upvotes: 2

Murtaza Manasawala
Murtaza Manasawala

Reputation: 725

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

Upvotes: 1

p.ajay
p.ajay

Reputation: 21

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

Upvotes: 1

aquatorrent
aquatorrent

Reputation: 841

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.

Upvotes: 63

ashu
ashu

Reputation: 31

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;

Upvotes: 3

Khurram Basharat
Khurram Basharat

Reputation: 71

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

Upvotes: 7

Islam Hamza
Islam Hamza

Reputation: 29

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

Upvotes: -6

Lomorng
Lomorng

Reputation: 521

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

Upvotes: 48

aF.
aF.

Reputation: 66757

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á :)

Upvotes: 3

Related Questions