| 1 | create view MealFlightDetails (FlightId, FlightNumber, Origin, Destination, MealId,
|
|---|
| 2 | Protein, Side, Salad, Snack, MealType, SeatClass, Quantity, Served, Remaining) as
|
|---|
| 3 | select flight.id, flight.flightnumber, departureAirport.code, arrivalAirport.code,
|
|---|
| 4 | meal.id, meal.protein, meal.side,
|
|---|
| 5 | meal.salad, meal.snack, mealtype.name, seatclass.name,servedmeals.quantity,
|
|---|
| 6 | servedmeals.served, servedmeals.quantity - servedmeals.served
|
|---|
| 7 | from flight
|
|---|
| 8 | join servedmeals on flight.id = servedmeals.flightid
|
|---|
| 9 | join meal on servedmeals.mealid = meal.id
|
|---|
| 10 | join mealtype on meal.mealtypeid = mealtype.id
|
|---|
| 11 | join gate as departureGate on departureGate.id = flight.actualgatedepartureid
|
|---|
| 12 | join gate as arrivalGate on arrivalGate.id = flight.actualgatearrivalid
|
|---|
| 13 | join terminal as departureTerminal on departureTerminal.id = departureGate.terminalid
|
|---|
| 14 | join terminal as arrivalTerminal on arrivalTerminal.id = arrivalGate.terminalid
|
|---|
| 15 | join airport as departureAirport on departureAirport.id = departureTerminal.airportid
|
|---|
| 16 | join airport as arrivalAirport on arrivalAirport.id = arrivalTerminal.airportid
|
|---|
| 17 | join seatclass on meal.seatclassid = seatclass.id;
|
|---|
