Mysql 对具有多列的三个表的连接查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13302898/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql join query on three tables with multiple columns
提问by Murtaza Malek
I have three tables like this:
我有三个这样的表：
Specialisation
专业化
sid | s_name
--------------
1 | test 1
2 | test 2
Person
人
pid | name | sid
------------------
1 | ABC | 1
2 | XYZ | 2
Timing
定时
tid | time_from | time_to | pid
----------------------------------
1 | 08:00:00 | 10:00:00 | 1
2 | 20:00:00 | 22:00:00 | 1
3 | 09:00:00 | 14:00:00 | 2
4 | 19:00:00 | 20:00:00 | 2
**I want to get result something like this*
**我想得到这样的结果*
pid | name | s_name | time_from | time_to
--------------------------------------------
1 | ABC | test 1 | 08:00:00 | 10:00:00
Description:
All three tables are connected.
I want all records where
specialisationid = '1'
personname Like'ABC'
timingis in between '08:00:00' and '10:00:00'.
I tried several combinations of mysql joins but not able to fetch the data correctly.
说明：
所有三个表都已连接。
我希望所有的记录，其中
专业化ID =“1”
的人的名字一样“ABC”
时间是在08年间：'10和：00：00 00' ：00。
我尝试了 mysql 连接的几种组合，但无法正确获取数据。
回答by John Woo
You can use INNER JOINfor this,
你可以用INNER JOIN这个，
SELECT a.pid, a.name,
b.sname,
c.time_from,
c.time_to
FROM person a
INNER JOIN specialisation b
ON a.sid = b.sid
INNER JOIN Timing c
ON a.pid = c.pid
WHERE a.sid = 1 and
a.name='ABC' AND
c.time_from >= '08:00:00' AND c.time_to <= '10:00:00'
回答by gks
回答by Shamis Shukoor
SELECT B.PID pid, B.NAME name, A.SNAME s_name, C.TIME_FROM time_from, C.TIME_TO time_to
FROM SPECIALISATION A, PERSON B, TIMING C
WHERE A.SID = 1 AND A.SID=B.SID AND B.PID=C.PID
AND C.TIME_FROM >=08:00:00 AND C.TIME_TO <=10.00.00
回答by Bajrang
Try :
尝试 ：
SELECT t.pid, p.name, s.s_name, t.time_from, t.time_to
FROM Specialisation AS s
left join Person AS p ON p.sid = s.sid
left join Timing AS t ON t.pid = p.pid
WHERE s.sid = 1
AND t.time_from >= 08:00:00' AND t.time_to <= 10:00:00
group by t.tid
回答by nawfal
This is a straight forward JOINwith appropriate WHEREcondition.
这是一个简单的JOIN适当WHERE条件。
SELECT t.pid, p.name, s.s_name, t.time_from, t.time_to
FROM Timing AS t
JOIN Person AS p ON p.pid = t.pid
JOIN Specialisation AS s ON s.sid = p.sid
WHERE s.sid = 1
AND t.time_from >= 08:00:00 AND t.time_to <= 10:00:00
This is a simple condition where you have to use JOINs to fetch data this way. Its so simple that you will get a hang of it right after your first use. May be this question and answers will help you understand it.
这是一个简单的条件，您必须使用JOINs 以这种方式获取数据。它非常简单，您将在第一次使用后立即掌握。可能这个问题和答案会帮助你理解它。
回答by AnandPhadke
SELECT p.pid, p.name, s.s_name, t.time_from, t.time_to
FROM Timing AS t
JOIN Person AS p ON p.pid = t.pid
JOIN specialisation AS s ON s.sid = p.sid
WHERE s.sid = 1 and p.name='ABC'
AND t.time_from >= '08:00:00' AND t.time_to <= '10:00:00'
