Complex SQL query - sum of a field in a joined table

Question

SQL Fiddle: http://sqlfiddle.com/#!2/b46ea/2

Current SQL:

SELECT `vehicle_journey`.*,
    ADDTIME(departure, SEC_TO_TIME((1))) AS stopDepartureTime
FROM `vehicle_journey`
INNER JOIN `vehicle_journey_days`
    ON `vehicle_journey_days`.`journey_reference` = `vehicle_journey`.`reference`
INNER JOIN `journey_pattern`
    ON `journey_pattern`.`reference` = `vehicle_journey`.`journey_pattern_reference`
INNER JOIN `journey_pattern_timing_link`
    ON `journey_pattern_timing_link`.`section` = `journey_pattern`.`journey_pattern_section`
WHERE `journey_pattern_timing_link`.`stop` = '1900HA030193'
    AND `vehicle_journey_days`.`day` = 'MondayToFriday'

The ultimate goal of this SQL statement is to get the bus arrival time based on its departuretime in vehicle_journeyand run_timeout journey_pattern_timing_link.

The reason this is difficult and why I ask for help is due to the nature of the table journey_pattern_timing_link. The table is configured to provide the time in seconds that it takes to move from one stop to the next (this is the time relative to the time of each departure).

select, . ADDTIME - , . "1" , . , , SEC_TO_TIME, .

, ? , journey_pattern_timing_link. , run_time journey_pattern_timing_link, LESS , where journey_pattern_timing_link. section .

, , , . SQL Fiddle .

Answer 1

, . journey_pattern_timing_link, , , , run_time departure:

SELECT 
  `vehicle_journey`.*,
  ADDTIME(departure,SEC_TO_TIME(SUM(`previous_stops`.`run_time`))) `duration`  
FROM `vehicle_journey`
INNER JOIN `vehicle_journey_days`
    ON `vehicle_journey_days`.`journey_reference` = `vehicle_journey`.`reference`
INNER JOIN `journey_pattern`
    ON `journey_pattern`.`reference` = `vehicle_journey`.`journey_pattern_reference`
INNER JOIN `journey_pattern_timing_link`
    ON `journey_pattern_timing_link`.`section` = `journey_pattern`.`journey_pattern_section`
LEFT JOIN `journey_pattern_timing_link` `previous_stops`
    ON `journey_pattern_timing_link`.`section` = `previous_stops`.`section`
    AND `journey_pattern_timing_link`.`id` >= `previous_stops`.`id`
WHERE `journey_pattern_timing_link`.`stop` = '1900HA080987'
    AND `vehicle_journey_days`.`day` = 'Saturday'
GROUP BY
  `vehicle_journey`.`id`,
  `vehicle_journey`.`journey_pattern_reference`,
  `vehicle_journey`.`departure`,
  `vehicle_journey`.`service_reference`,
  `vehicle_journey`.`reference`,
  `vehicle_journey`.`code`

Answer 2

, SQL, , : -

SELECT vehicle_journey.id, 
        vehicle_journey.journey_pattern_reference, 
        vehicle_journey.departure, 
        vehicle_journey.service_reference, 
        vehicle_journey.reference, 
        vehicle_journey.code, 
        vehicle_journey_days.day,
        TIME(ADDTIME(departure, SEC_TO_TIME(SUM(jptl2.run_time)))) AS stopDepartureTime
FROM vehicle_journey
INNER JOIN vehicle_journey_days ON vehicle_journey_days.journey_reference = vehicle_journey.reference
INNER JOIN journey_pattern ON journey_pattern.reference = vehicle_journey.journey_pattern_reference
INNER JOIN journey_pattern_timing_link ON journey_pattern_timing_link.section = journey_pattern.journey_pattern_section AND journey_pattern_timing_link.stop = '1900HA080102' 
INNER JOIN journey_pattern_timing_link  jptl2 ON jptl2.section = journey_pattern.journey_pattern_section AND jptl2.ID <= journey_pattern_timing_link.ID
WHERE vehicle_journey_days.day = 'Saturday'
GROUP BY vehicle_journey.id, vehicle_journey.journey_pattern_reference, vehicle_journey.departure, vehicle_journey.service_reference, vehicle_journey.reference, vehicle_journey.code