title: Display FlightTrip and FlightStop Details using views
body: <p>I would like to extract data from a specific table . However the constrain is i need to u 2 relational views to get the data. I am able to get the data without using view, but the requirement is to use views. And i new to db. I hope you guys can help me. Thus i think i need to use data from 2 tables. But from my understanding i dont think i need to use 2 tables. Hope u guys cand advice. As i said earlier i need to use 2 views to extract the data.</p>
  
  <pre><code> FlightTripNo Departure Destination FinalDestination
   - - - - - - - - - - - - - - - -
   05 Singapore Bangalore Chennai
   03 Sdyney Auckland
  </code></pre>
  
  <p>This is the following table.</p>
  
  <pre><code>FLIGHTTRIP( FlightTripNo,PlaneNo)
  PRIMARY KEY (FlightTripNo),
  
  ( 1, JET348)
  ( 2, TR1313)
  ( 3, TR287)
  ( 4, MY871P)
  ( 5, SQ777)
  ( 6, AI322X)
  </code></pre>
  
  <p>FLIGHTSTOP TABLE</p>
  
  <pre><code>FLIGHTSTOP(  FlightTripNo, FlightStopNo, DEPARTURE, DESTINATION )    
  PRIMARY KEY (FlightTripNo, FlightStopNo)
  UNIQUE UNIQUE(FlightTripNo, DEPARTURE, DESTINATION)
  FOREIGN KEY (FlightTripNo) REFERENCES FLIGHTTRIP(FlightTripNo)
  
  INSERT INTO FLIGHTSTOP VALUES( 1, 1, 'Singapore', 'Malaysia');
  INSERT INTO FLIGHTSTOP VALUES( 1, 2, 'Malaysia', 'Chennai');
  INSERT INTO FLIGHTSTOP VALUES( 1, 3, 'Chennai', 'Bangalore');
  INSERT INTO FLIGHTSTOP VALUES( 3, 1, 'Syndey', 'Auckland');
  INSERT INTO FLIGHTSTOP VALUES( 5, 1, 'Singapore', 'Bangalore');
  INSERT INTO FLIGHTSTOP VALUES( 5, 2, 'Bangalore', 'Chennai');
  ......
  Total 400 records
  </code></pre>
  
  <p>This how far i have done without using views. But i need to use views. And 2 view. Please advice.</p>
  
  <pre><code> SELECT FLIGHTSTOP.FlightTripNo,FLIGHTSTOP.FlightStopNo, FLIGHTSTOP.DEPARTURE AS ORIGIN,  FLIGHTSTOP.DESTINATION AS DESTINATION1, T2.DESTINATION AS DESTINATION2
   FROM FLIGHTSTOP LEFT JOIN FLIGHTSTOP T2 ON FLIGHTSTOP.FlightTripNo = T2.FlightTripNo AND   FLIGHTSTOP.DEPARTURE = T2.DEPARTURE
   WHERE FLIGHTSTOP.FlightTripNo IN (SELECT FlightTripNo FROM FLIGHTSTOP
                  GROUP BY FlightTripNo
                  HAVING COUNT(*) &lt; 3)
                  ORDER BY FLIGHTSTOP.FlightTripNo;
  </code></pre>