I have following tables
routes
id | route_name stops
id | stop_name stop_orders
id | route_id | stop_id | stop_order here i will store all stops name
buses
id | bus_name 1 | R.S here i will store all bus names
3.route_connect
id | bus_id | stop_id 1 | 1 | 1 2 | 1 | 2 bus_timing
id | route_id | stop_id | bus_id | bus_timing | trip | trip_direction bus_name|stop_name
R.S |Sydney R.S | Melbourne 1) My aim is to get all bus names when user will search between two stops.
2) For example if user search for source to destination (Sydney to Melborne) then it should return all bus names which is traveling between these two city. If there is no direct buses then i need to show connected buses between two city
I am trying write a query where i need to retrieve all buses between two stop and between two timings
i tried lots of combination of query but it failed to retrive the expected result
1) I need to retrieve all buses which travel between two city along with between two times.
2) Also if buses are not directly travel between two city then i need to show inter connected buses.
I have tried following query
SELECT buses.bus_name,stops.stop_name FROM route_connect LEFT JOIN buses ON buses.id=route_connect.bus_id LEFT JOIN stops ON stops.id=route_connect.stop_id WHERE route_connect.stop_id=(select id from stops where stops.stop_name='Sydney') AND route_connect.stop_id=(select id from stops where stops.stop_name='Melbourne') but this will return null .If i use OR then it will return
Can any one help me how i can fix this issue .Is my table structure is wrong ?
Note: here i am tagging both mysql and php as well as framework since i m okay with answer both query as well as code also
SELECT * FROM bus_timings INNER JOIN buses on buses.id=bus_timings.bus_id INNER JOIN routes ON routes.id=bus_timings.route_id INNER JOIN stops ON stops.id=bus_timings.stop_id WHERE stops.stop_name in ('Sydney', 'Melbourne') and bus_timings.bus_time BETWEEN CAST('09:30:00' AS time) AND CAST('10:00:00' AS time) still buses more than two buses traveling between two city but return only one
SELECT * FROM bus_timings INNER JOIN buses on buses.id=bus_timings.bus_id INNER JOIN routes ON routes.id=bus_timings.route_id INNER JOIN stops ON stops.id=bus_timings.stop_id WHERE stops.stop_name in ('Sydney', 'Melbourne') HAVING COUNT(DISTINCT stops.stop_name) = 2 still buses more than two buses traveling between two city but return only one
for more detail i have another question PHP MYSQL retrieve data based on two matching row column
Updated routes table contain
+-----------------+ |id | route_name | +----+------------- | 1 | Sydney | +----+------------+ | 2 | Melborne | +----+------------+ stops table contain
+-----------------+ | id | stop_name | +----+------------- | 1 | stop_1 | +----+------------+ | 2 | stop_2 | +----+------------+ stop_orders
+------+-----------+----------+-------------+ | id | route_id | stop_id | stop_orders | +------+-----------+----------+-------------+ | 1 | 1 | 1 | 1 | +------+-----------+----------+-------------+ | 2 | 1 | 2 | 2 | +------+-----------+----------+-------------+ bus_timing
+-----+----------+---------+--------+------------+--------+----------------+ | id | route_id | stop_id | bus_id | bus_timing | trip | trip_direction | +-----+----------+---------+--------+------------+--------+----------------+ | 1 | 1 | 1 | 1 | 07:30:00 | 1 | 1 | +-----+----------+---------+--------+------------+--------+----------------+ | 2 | 1 | 2 | 1 | 07:50:00 | 1 | 1 | +-----+----------+---------+--------+------------+--------+----------------+ 1)routes will contain different routes like sydney,melborne.
2)stops will contain same as routes
3)stop_order table contain each route as multiple stops with the order so we can know which is then next stop based on order
4)each bus has timing which will be mentioned in bus timing table with route and stop 5)one bus will travel multiple times between two city so i added trip and direction will identify which way bus is traveling i mean sydney to melborne or melborne to sydney
Updated
Image explanation
1)In image city refers to routes table
2)green color shows multiple stops in city
3)city 1 and city 2 will connect in city 3 so if a person wants to go city-1 to city-2 then he need to use interconnected buses between city-1 to city-3 and city-3 to city-2
Updated Table structure
CREATE TABLE `buses` ( `id` int(11) NOT NULL, `bus_name` varchar(255) DEFAULT NULL, `bus_number` varchar(255) DEFAULT NULL, `bus_image` varchar(255) DEFAULT NULL, `bus_stop_start` int(11) DEFAULT NULL, `bus_stop_end` int(11) DEFAULT NULL, `route_id` varchar(255) DEFAULT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `buses` -- INSERT INTO `buses` (`id`, `bus_name`, `bus_number`, `bus_image`, `bus_stop_start`, `bus_stop_end`, `route_id`, `created_at`, `updated_at`) VALUES (1, 'A.K', NULL, NULL, NULL, NULL, '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (2, 'B.K', NULL, NULL, NULL, NULL, '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'); (3, 'J.K', NULL, NULL, NULL, NULL, '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'); (4, 'M.K', NULL, NULL, NULL, NULL, '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'); (5, 'N.K', NULL, NULL, NULL, NULL, '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'); CREATE TABLE `bus_timings` ( `id` int(11) NOT NULL, `route_id` int(11) NOT NULL, `bus_id` int(11) NOT NULL, `stop_id` int(11) NOT NULL, `bus_time` varchar(255) NOT NULL, `bus_direction` varchar(255) NOT NULL, `trip` varchar(255) NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `bus_timings` -- INSERT INTO `bus_timings` (`id`, `route_id`, `bus_id`, `stop_id`, `bus_time`, `bus_direction`, `trip`, `created_at`, `updated_at`) VALUES (8, 1, 1, 4, '08:26:00', '1', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (9, 1, 1, 5, '08:45:00', '1', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (21, 1, 1, 18, '10:45:00', '1', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (22, 1, 1, 1, '10:50:00', '2', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (23, 1, 1, 27, '10:55:00', '2', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (24, 1, 1, 2, '11:05:00', '2', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (25, 1, 1, 20, '11:15:00', '2', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (26, 1, 1, 3, '11:20:00', '2', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (27, 1, 1, 6, '11:25:00', '2', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (28, 1, 1, 19, '11:35:00', '2', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (29, 1, 1, 4, '11:45:00', '2', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (30, 1, 1, 5, '11:55:00', '2', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (31, 1, 1, 21, '11:58:00', '2', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (32, 1, 1, 7, '12:05:00', '2', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (33, 1, 1, 22, '12:15:00', '2', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (34, 1, 1, 23, '12:20:00', '2', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), CREATE TABLE `routes` ( `id` int(11) NOT NULL, `route_name` varchar(255) DEFAULT NULL, `seo_url` varchar(255) NOT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `routes` -- INSERT INTO `routes` (`id`, `route_name`, `seo_url`, `created_at`, `updated_at`) VALUES (1, 'Sydney', 'sydney', '2017-12-26 08:46:16', '2017-12-26 08:46:16'), (2, 'Melbourne', 'melbourne', '2017-12-26 08:46:16', '2017-12-26 08:46:16'), (3, 'Perth', 'perth', '2017-12-26 08:46:16', '2017-12-26 08:46:16'); CREATE TABLE `route_connect` ( `id` int(11) NOT NULL, `stop_id` int(11) DEFAULT NULL, `bus_id` int(11) DEFAULT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `route_connect` -- INSERT INTO `route_connect` (`id`, `stop_id`, `bus_id`, `created_at`, `updated_at`) VALUES (1, 2, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (2, 8, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (3, 14, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (4, 18, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (5, 14, 4, '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (6, 18, 4, '0000-00-00 00:00:00', '0000-00-00 00:00:00'), -- -------------------------------------------------------- -- -- Table structure for table `stops` -- CREATE TABLE `stops` ( `id` int(11) NOT NULL, `stop_name` varchar(255) DEFAULT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `stops` -- INSERT INTO `stops` (`id`, `stop_name`, `created_at`, `updated_at`) VALUES (1, 'stop_1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (2, 'stop_2', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (3, 'stop_3', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (4, 'stop_4', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (5, 'stop_5', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (6, 'stop_6', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (7, 'stop_7', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (8, 'stop_8', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (9, 'stop_9', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (10, 'stop_10', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (11, 'stop_20', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (12, 'stop_21', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (13, 'stop_22', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (14, 'stop_23', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (15, 'stop_25', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (16, 'stop_37', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (17, 'stop_n1', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), -- -------------------------------------------------------- -- -- Table structure for table `stop_orders` -- CREATE TABLE `stop_orders` ( `id` int(11) NOT NULL, `route_id` int(11) DEFAULT NULL, `stop_id` int(11) DEFAULT NULL, `stop_orders` int(11) DEFAULT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `stop_orders` -- INSERT INTO `stop_orders` (`id`, `route_id`, `stop_id`, `stop_orders`, `created_at`, `updated_at`) VALUES (1, 1, 1, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (2, 1, 27, 2, '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (3, 1, 2, 3, '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (4, 1, 20, 4, '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (5, 1, 3, 5, '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (6, 1, 6, 6, '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (7, 1, 19, 7, '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (8, 1, 4, 8, '0000-00-00 00:00:00', '0000-00-00 00:00:00'), Update-2 Gordon Linoff answer at one works fine.and that query not giving result when user select large time difference like 1 to 9 .This is because same bus will travel multiple times so group by will kill here if i remove group by then only one record will come even i tried many combination of group by
Updated
My project aim is to store all buses ,lines,stops and bus timing along with trip and direction. 1.There are many routes and each routes have multiple stops and few bus stops may belong to multiple routes (For example city 1 and city 2 route buses will travel in city 3 routes as per image provided)
2.in bus timing table i will store buses time along with route(line),stops trip and direction(Here trip and direction used because of one buse will travel between source to destination multiple times a day)
Output expecting
1.when user search between source to destination along with the time then application should return all buses travel between source and destination (if user select long time for example
2 clock to 12 clock )then it should return all buses here there are chances same buses result twice or more than that because every bus will travel more than twice
1 Answers
Answers 1
Because stop_id cannot be two different values in the same row.
Aggregation is one way to do what you want:
SELECT b.bus_name FROM buses b JOIN route_connect rc ON rc.busid = b.id JOIN stops s ON s.id = rc.stop_id GROUP BY b.bus_name HAVING SUM( s.stop_name = 'Sydney' ) > 0 AND SUM( s.stop_name = 'Melbourne' ) > 0; This returns buses that have stops with the name of both cities.
Given that buses can have lots of stops, it might be more efficient to do:
SELECT b.bus_name FROM buses b JOIN route_connect rc ON rc.busid = b.id JOIN stops s ON s.id = rc.stop_id WHERE s.stop_name in ('Sydney', 'Melbourne') GROUP BY b.bus_name HAVING COUNT(DISTINCT s.stop_name) = 2; 
0 comments:
Post a Comment