Mysql join query on three tables with multiple columns

Question

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.

what is the data time time_from and time_to?

John Woo
– John Woo

2012-11-09 06:07:37 +00:00
Commented Nov 9, 2012 at 6:07 — John Woo
– John Woo, Commented Nov 9, 2012 at 6:07

John Woo · Accepted Answer · 2012-11-09 06:10:12Z

23

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

edited Nov 9, 2012 at 6:10

answered Nov 9, 2012 at 6:02

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Woo Over a year ago

@AnandPhadke they both perform the same since we are both using INNER JOIN.

thar45 · Accepted Answer · 2012-11-09 06:42:24Z

1

You can use Simple Join like this

     Select P.pid , name , s_name ,time_from ,
     time_to from Specialisation S 
     Join Person P on P.sid=S.sid 
     Join Timing T on T.pid= P.pid 
     where T.time_from >= '08:00:00' AND T.time_to <='10:00:00' And P.name='ABC';

Fiddle

edited Nov 9, 2012 at 6:42

answered Nov 9, 2012 at 6:07

thar45

3,5705 gold badges35 silver badges49 bronze badges

Comments

Shamis Shukoor · Accepted Answer · 2012-11-09 06:09:57Z

0

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

answered Nov 9, 2012 at 6:09

Shamis Shukoor

2,5075 gold badges29 silver badges34 bronze badges

Comments

Bajrang · Accepted Answer · 2012-11-09 06:10:18Z

0

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

answered Nov 9, 2012 at 6:10

Bajrang

8,6795 gold badges29 silver badges40 bronze badges

Comments

nawfal · Accepted Answer · 2012-11-09 06:34:01Z

0

This is a straight forward JOIN with appropriate WHERE condition.

 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.

edited Nov 9, 2012 at 6:34

answered Nov 9, 2012 at 6:04

nawfal

73.8k59 gold badges342 silver badges379 bronze badges

1 Comment

nawfal Over a year ago

Why downvote? :o. If not the perfect answer, doesn't this guide OP to right direction? And pretty much all the answers that came after uses the same ditto code!

AnandPhadke · Accepted Answer · 2012-11-09 06:42:34Z

0

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'

edited Nov 9, 2012 at 6:42

answered Nov 9, 2012 at 6:06

AnandPhadke

13.5k5 gold badges28 silver badges34 bronze badges

2 Comments

thar45 Over a year ago

pid will be ambiguous to select use alias @AnandPhadke

AnandPhadke Over a year ago

Thangs-Thanks for poining out this

Collectives™ on Stack Overflow

Mysql join query on three tables with multiple columns

6 Answers 6

1 Comment

Comments

Comments

Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related