SQL 内连接与 3 个表？

SQL Inner-join with 3 tables?

提问by Bob Sanders

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

我正在尝试在一个视图中加入 3 个表；这是情况：

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...).

我有一张表格，其中包含申请住在这个大学校园的学生的信息。我有另一个表格，列出了每个学生的大厅首选项（其中 3 个）。但是这些偏好中的每一个都只是一个 ID 号，而这个 ID 号在第三个表中有一个对应的 Hall Name（这个数据库没有设计……）。

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

差不多，我INNER JOIN在桌子上有他们的喜好和他们的信息，结果就像......

 John Doe | 923423 | Incoming Student | 005

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

005将在哪里HallID。所以现在我想将它HallID与第三个表相匹配，该表包含一个HallID和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

回答by Taryn

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:

根据您对多个大厅的要求，您可以这样做。您只需为每个房间首选项 ID 多次加入 Hall 表：

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

回答by Lomorng

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

回答by aquatorrent

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

如果您有 3 个表ID要加入相同的表，我认为它会是这样的：

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.

只需替换*为您想从表中获取的内容即可。

回答by Khurram Basharat

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

回答by aF.

You just need a second inner join that links the ID Numberthat you have now to the ID Numberof the third table. Afterwards, replace the ID Numberby the Hall Nameand voilá :)

您只需要第二个内部连接，将ID Number您现在拥有ID Number的 连接到第三个表的 。此后，更换ID Number由Hall Name瞧:)

回答by Nathan

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.

有很多回应，但总体教训似乎是您可以在 where 子句中使用多个 JOINS；如果您有其他问题并且只想尝试解决问题，那么 techonthenet.com（我的老板向我推荐它，这就是我发现它的方式）也有很好的 SQL 教程。

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

回答by ashu

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;

回答by p.ajay

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

回答by Murtaza Manasawala

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

回答by Sri Siva

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

这是对具有相同 id 的 join 3 表的正确查询**

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

员工第一张桌子。报告第二个表。出生第三表