Mysql join query on three tables with multiple columns

join, mysql, relational-database

Solution

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'

- SQLFiddle Demo

Problem

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.

Original source