MySQL Multiple select from same table

Question

I am building a bus reservation system. I am trying to query out a bus based on the trip selected. I have in the tables Departure and Arrival for storing time. I need to query out the Departure and the Arrival.

Below is my table schema

CREATE TABLE `bus_details` (
  `ID` int(11) NOT NULL,
  `Route` varchar(60) NOT NULL,
  `RouteCode` int(11) NOT NULL,
  `BusCode` int(11) NOT NULL,
  `CityCode` int(11) NOT NULL,
  `City` varchar(20) NOT NULL,
  `Departure` time DEFAULT NULL,
  `Arrival` time DEFAULT NULL,
  `FromCityCode` int(11) NOT NULL,
  `ToCityCode` int(11) NOT NULL,
  `BusName` varchar(30) NOT NULL,
  `sValid` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `bus_details` (`ID`, `Route`, `RouteCode`, `BusCode`, `CityCode`, `City`, `Departure`, `Arrival`, `FromCityCode`, `ToCityCode`, `BusName`, `sValid`) VALUES
(48, 'Accra Mall - Papaye', 10001, 1001, 101, 'Accra Mall', '01:00:00', NULL, 101, 101, 'Sprinter', 1),
(49, 'Accra Mall - Papaye', 10001, 1001, 102, 'Flower Pot', '00:30:00', '01:15:00', 101, 102, 'Sprinter', 0),
(50, 'Accra Mall - Papaye', 10001, 1001, 103, 'Palace', '02:00:00', '00:45:00', 102, 103, 'Sprinter', 0),
(51, 'Accra Mall - Papaye', 10001, 1001, 104, 'Papaye', NULL, '02:30:00', 103, 104, 'Sprinter', 1),
(52, 'Accra Mall - Papaye', 10001, 1003, 101, 'Accra Mall', '02:00:00', NULL, 101, 101, 'VVIP Bus', 1),
(53, 'Accra Mall - Papaye', 10001, 1003, 102, 'Flower Pot', '02:30:00', '02:15:00', 101, 102, 'VVIP Bus', 0),
(54, 'Accra Mall - Papaye', 10001, 1003, 103, 'Palace', '03:00:00', '02:45:00', 102, 103, 'VVIP Bus', 0),
(55, 'Accra Mall - Papaye', 10001, 1003, 104, 'Papaye', NULL, '03:15:00', 103, 104, 'VVIP Bus', 1);

ALTER TABLE `bus_details`
  ADD PRIMARY KEY (`ID`);

I tried with

SELECT DISTINCT(t1.BusCode), t1.BusName, t1.CityCode, t1.FromCityCode, t2.ToCityCode, t1.Departure, t2.Arrival
FROM
    (SELECT BusName, BusCode, CityCode, FromCityCode, ToCityCode, Departure From bus_details Where CityCode IN(101) AND FromCityCode IN(101) Group By BusCode) As t1,
    (SELECT BusName, BusCode, CityCode, FromCityCode, ToCityCode, Arrival From bus_details Where CityCode IN(104) AND ToCityCode IN(104) Group By BusCode) As t2

Which was close to my expected answer but I returns 4 results as I expect to because only two buses are on this trip.

Within the four results two are correct and two is not.

Please can you help me with a correct query for this operation.

Thank you in advance

**Expected Output**
BusName   | tripFrom | tripTo | Departure | Arrival 
Sprinter     101          104    1:00:00    2:30:00    
VVIP Bus     101          104    2:30:00    3:15:00

This a sample of what I want my output to be. Thanks again

Which was close to my expected answer but I returns 4 results as I expected 2 results because only two buses are on this trip. ---edited--- — Mohammed
– Mohammed, Commented May 22, 2018 at 11:15
I want the BusName, tripFromCode, tripToCode, Departure And Arrival Example as BusName | tripFrom | tripTo | Departure | Arrival Sprinter 101 104 1:00:00 2:30:00 VVIP Bus 101 104 2:30:00 3:15:00 This is what I expect my output to be — Mohammed
– Mohammed, Commented May 22, 2018 at 11:22
Edit you question and add the expected output within the question dont use a comment for that. — Raymond Nijland
– Raymond Nijland, Commented May 22, 2018 at 11:28

Nick · Accepted Answer · 2018-05-22 23:38:37Z

The problem with your query is that you are using a JOIN with no condition, so it is creating a cross-product of 2 bus routes x 2 bus routes = 4 results. If you had 3 routes you would have got 9 results. If you had included t2.BusName in your SELECT you would have seen all the cases where it was different to t1.BusName. You need to restrict the result by adding a condition that ensures the bus is the same on both routes i.e. t1.BusCode = t2.BusCode (or t1.BusName = t2.BusName)

SELECT t1.BusName, t1.FromCityCode AS tripFrom, t2.ToCityCode AS tripTo, t1.Departure, t2.Arrival
FROM
    (SELECT BusName, BusCode, CityCode, FromCityCode, ToCityCode, Departure 
     FROM bus_details 
     WHERE CityCode IN(101) AND FromCityCode IN(101) 
     GROUP BY BusCode) As t1
JOIN
    (SELECT BusName, BusCode, CityCode, FromCityCode, ToCityCode, Arrival 
     FROM bus_details 
     WHERE CityCode IN(104) AND ToCityCode IN(104)
     GROUP BY BusCode) As t2
ON t1.BusCode = t2.BusCode

Output (Demo):

BusName     tripFrom    tripTo  Departure   Arrival
Sprinter    101         104     01:00:00    02:30:00
VVIP Bus    101         104     02:00:00    03:15:00

Collectives™ on Stack Overflow

MySQL Multiple select from same table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related