CODEWITHSUNDEEP
phpmysql
Vadivel S
Vadivel S

Reputation: 660

Need Mysql Query for Search Bus from stop and To Stop

I create Table for Buses and Route and Stop tables

Buses Table

id ,  bus_name
 1      AAA
 2      BBB

Stop Table

id, stop_name, latitude, longitude
 1    XYZ        12.5555    77.222
 2    SSS        13.5555    77.2888
 3    EEE        88.444     77.222
 4    C          34.8859309  -97.47070
 5    JJJ        32.9902355   -97.99804

route Table

id, bus_id,  stop_id, arrival_time,   departure_time,     START_END_STOP
  1      1        1         0000            8.00AM        START
  2      1        2        8.10AM          8.10AM 
  3      1        3        8.15AM          8.16AM
  4      1        4        8.20AM          8.20AM 
  5      1        5        8.25AM          0000            END
  6      2        1        0000            8.10AM          START
  7      2        3        8.15AM          8.18AM
  8      2        5        8.27AM           0000           END

Now I search from Stop XYZ to JJJ

I Want Result is

bus_name  from_stop_name  departure_time  to_stop_name  arrival_time  Notes
  AAA       XYZ             8.00AM             JJJ            8.25AM   NIL
  BBB       XYZ             8.10AM             JJJ            8.27AM   NIL

Or I search Stop from XYZ to C

bus_name  from_stop_name   departure_time  to_stop_name arrival_time  Notes
  AAA       XYZ             8.00AM             C         8.20AM        NIL
  BBB       XYZ             8.10AM             JJJ       8.27AM        JJJ to C is 600 Mts

I Need Mysql Selecting Query , Please help Me. thanks

Upvotes: 1

Views: 479

Answers (3)

Beginner
Beginner

Reputation: 4153

enter image description here

enter image description here

enter image description here

the result of the query

enter image description here

SELECT `B`.`bus_name`, 
       `from_stop`.`stop_name` AS `from_top_time`, 
       `Departure`.`departure_time`, 
       `to_stop`.`stop_name`   AS `to_stop_name`, 
       `Arrival`.`arrival_time` 
FROM   `buses` `B` 
       JOIN `route` `Departure` 
         ON `Departure`.`bus_id` = `B`.`id` 
       JOIN `route` `Arrival` 
         ON `Arrival`.`bus_id` = `B`.`id` 
       JOIN `stop` `from_stop` 
         ON `Departure`.`stop_id` = `from_stop`.`id` 
       JOIN `stop` `to_stop` 
         ON `Arrival`.`stop_id` = `to_stop`.`id` 
WHERE  ( `from_stop`.`stop_name` = 'XYZ' 
         AND `to_stop`.`stop_name` = 'JJJ' ) 
        OR ( `from_stop`.`stop_name` = 'XYZ' 
             AND `to_stop`.`stop_name` = 'C' ) 
GROUP  BY `B`.`id`

Upvotes: 1

Kapil Jain
Kapil Jain

Reputation: 188

You can try below query. we need to write input param 'stop_name' at 4 places in query. Table name are buses, route and stops used in below query

select A.name, A.departure_time, A.from_stop_name, RO.arrival_time,
'JJJ' as to_stop_name  from 
 (select bus_id, name, departure_time,
 stop_name as from_stop_name from route R left join stops S on
 R.stop_id = S.id left join buses B on bus_id = B.id where stop_id in
 (select id from stops where stop_name in ('XYZ', 'JJJ')) group by
 bus_id having count(distinct stop_id) = 2) A 
left join 
 route RO on 
 A.bus_id = RO.bus_id and RO.stop_id in (SELECT id from stops where 
 stop_name = 'JJJ');

Upvotes: 1

No'am Newman
No'am Newman

Reputation: 6477

I think that the query below will give you the desired results. It would be better to parameterize the inputs as to make the query more general.

select buses.bus_name, s1.stop_name as from_stop_name, t1.departure_time, 
s2.stop_name as to_stop_name, t2.arrival_time
from buses
inner join table t1 on t1.bus_id = buses.id
inner join stop s1 on s1.id = t1.stop_id
inner join table t2 on t2.bud_id = buses.id
inner join stop s2 on s2.id = t2.stop_id
where buses.bus_name in ('AAA', 'BBB')
and s1.stop_name = 'XYZ'
and s2.stop_name = 'JJJ'

Upvotes: 1

Related Questions