title: database query to find trains between two stations on specific date like irctc.co.in
body: <p>I have following database</p>
  
  <h3>Table structure for table <code>trains</code></h3>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `trains` (
      `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
      `train_no` varchar(5) COLLATE latin1_general_ci DEFAULT NULL,
      `train_name` varchar(50) COLLATE latin1_general_ci NOT NULL,
      `runsfrom` varchar(50) COLLATE latin1_general_ci NOT NULL,
      `SUN` varchar(3) COLLATE latin1_general_ci NOT NULL,
      `MON` varchar(3) COLLATE latin1_general_ci NOT NULL,
      `TUE` varchar(3) COLLATE latin1_general_ci NOT NULL,
      `WED` varchar(3) COLLATE latin1_general_ci NOT NULL,
      `THU` varchar(3) COLLATE latin1_general_ci NOT NULL,
      `FRI` varchar(3) COLLATE latin1_general_ci NOT NULL,
      `SAT` varchar(3) COLLATE latin1_general_ci NOT NULL,
      `DOE` date NOT NULL,
      PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1912 ;
  </code></pre>
  
  <p>having data like this:</p>
  
  <pre><code>INSERT INTO `trains` VALUES (269, '12307', 'HWH JU  EXPRESS', 'HOWRAH JN', 'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT', 'SUN', '2013-03-24');
  INSERT INTO `trains` VALUES (270, '12308', 'JU HWH SUPFAST', 'JODHPUR JN', 'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT', 'SUN', '2013-03-24');
  INSERT INTO `trains` VALUES (381, '12461', 'MANDOR EXPRESS', 'DELHI', 'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT', 'SUN', '2013-03-24');
  INSERT INTO `trains` VALUES (382, '12462', 'MANDOR EXPRESS', 'JODHPUR JN', 'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT', 'SUN', '2013-03-24');
  </code></pre>
  
  <hr>
  
  <h3>Table structure for table <code>train_number</code></h3>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `train_number` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `train_no` varchar(5) NOT NULL,
      PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1912 ;
  </code></pre>
  
  <p>having data like this:</p>
  
  <pre><code>INSERT INTO `train_number` VALUES (269, '12307');
  INSERT INTO `train_number` VALUES (270, '12308');
  INSERT INTO `train_number` VALUES (381, '12461');
  </code></pre>
  
  <h3>Table structure for table <code>train_schedule</code></h3>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `train_schedule` (
      `train_no` varchar(5) NOT NULL,
      `stn_code` varchar(20) NOT NULL,
      `stn_name` varchar(50) NOT NULL,
      `route_no` varchar(2) NOT NULL,
      `arr_time` varchar(5) NOT NULL,
      `dep_time` varchar(5) NOT NULL,
      `halt_time` varchar(5) NOT NULL,
      `distance` varchar(4) NOT NULL,
      `day` varchar(2) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>having data like this:</p>
  
  <pre><code>INSERT INTO `train_schedule` VALUES ('12307', 'HWH ', 'HOWRAH JN      ', '1', 'Sourc', '23:30', '', '0', '1');
  INSERT INTO `train_schedule` VALUES ('12307', 'BWN ', 'BARDDHAMAN JN  ', '1', '00:35', '00:37', '2:00', '95', '2');
  INSERT INTO `train_schedule` VALUES ('12307', 'ASN ', 'ASANSOL JN     ', '1', '01:52', '01:56', '4:00', '200', '2');
  INSERT INTO `train_schedule` VALUES ('12307', 'DHN ', 'DHANBAD JN     ', '1', '03:05', '03:15', '10:00', '259', '2');
  </code></pre>
  
  <p>Now I want to find to find the trains between two stations <strong>on specific date</strong>. So I tried with this query</p>
  
  <pre><code>SELECT distinct d1.train_no
  FROM   train_schedule d1
  INNER JOIN train_schedule d2 ON d2.train_no=d1.train_no
  WHERE d1.stn_code = 'JU' and d2.stn_code = 'JP'
  </code></pre>
  
  <p>But it's showing both data from JU to JP and from JP to JU also so it makes the result double.</p>
  
  <p>I want to make this query correct only for one direction  <strong>on a specific date</strong> as days when it runs is also given in database</p>