mysql链接三个表查询_Mysql连接查询三个具有多列的表

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

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.

解决方案

You can use INNER JOIN for this,

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'