MySQL对具有多列的三个表进行联接查询 [英] Mysql join query on three tables with multiple columns

问题描述

我有三个这样的表:

专业化

sid | s_name
--------------
 1  | test 1
 2  | test 2  

人员

pid | name | sid
------------------
 1  | ABC  |  1
 2  | XYZ  |  2

定时

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

说明:
三个表都已连接.
我想要所有记录在哪里
专业化 id ='1'
人姓名类似"ABC"
时间在"08:00:00"和"10:00:00"之间.

我尝试了几种mysql joins组合，但无法正确获取数据.

Description:
All three tables are connected.
I want all records where
specialisation id = '1'
person name Like 'ABC'
timing is in between '08:00:00' and '10:00:00'.

I tried several combinations of mysql joins but not able to fetch the data correctly.

推荐答案

您可以为此使用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'

• SQLFiddle演示

这篇关于MySQL对具有多列的三个表进行联接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！