title: MySQL Multiple select from same table
body: <p>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.</p>
  
  <p>Below is my table schema</p>
  
  <pre><code>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`);
  </code></pre>
  
  <p>I tried with </p>
  
  <pre><code>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
  </code></pre>
  
  <p>Which was close to my expected answer but I returns 4 results as I expect to because only two buses are on this trip.</p>
  
  <p>Within the four results two are correct and two is not.</p>
  
  <p>Please can you help me with a correct query for this operation.</p>
  
  <p>Thank you in advance</p>
  
  <pre><code>**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 
  </code></pre>
  
  <p>This a sample of what I want my output to be.
  Thanks again</p>