How to join three database tables in sql

Question

I have these three database tables

CREATE TABLE IF NOT EXISTS `bus_info` (
  `bus_id` int(11) NOT NULL,
  `bus_no` varchar(20) NOT NULL
)


INSERT INTO `bus_info` (`bus_id`, `bus_no`) VALUES
(1, 'Mh10 BD 5209');

CREATE TABLE IF NOT EXISTS `route_info` (
  `route_id` int(11) NOT NULL,
  `route_name` varchar(100) NOT NULL,
  `latitude` varchar(200) NOT NULL,
  `longitude` varchar(200) NOT NULL
)


INSERT INTO `route_info` (`route_id`, `route_name`, `latitude`, `longitude`) VALUES
(1, 'Sangli Stand', '16.852903', '74.562999'),
(2, 'Maruti Chowk', '16.858100', '74.562460'),
(3, 'Ganpati Mandir', '16.862211', '74.559501'),
(4, 'College Corner ', '16.862854', '74.576579'),
(5, 'Ram Mandir ', '16.856413', '74.574738'),
(6, 'D mart', '16.845782', '74.576866'),
(7, 'Vishrambag sangli', '16.846022', '74.602854'),
(8, 'Bharat Mill', '16.865029', '74.607000'),
(9, 'Bharti hospital', '16.840008', '74.618400'),
(10, 'SBGI', '16.835329', '74.624400');

CREATE TABLE IF NOT EXISTS `route_detail` (
  `rd_id` int(11) NOT NULL,
  `bus_id` int(11) NOT NULL,
  `route_id` int(11) NOT NULL
)

INSERT INTO `route_detail` (`rd_id`, `bus_id`, `route_id`)
    VALUES (1, 1, 1),
           (2, 1, 2);

I am assigning a particular route for a single bus there may have multiple routes for single bus then i need a query for selecting a route for selected bus

for ex:

from database in route_detail table there are assigned two route for same bus then I need columns from above three tables are

bus_id,bus_no, route_id,latitude,longitude

Answer 1

I have done it.....

SELECT 
    bus_info.bus_no, route_detail.route_id, route_info.latitude, 
    route_info.longitude 
FROM 
    (bus_info 
INNER JOIN 
    route_detail ON bus_info.bus_id = route_detail.bus_id) 
INNER JOIN 
    route_info ON route_detail.route_id = route_info.route_id