| 1 | create view TicketDetails (PassengerId, Name, Surname, DateOfBirth, Email, AssistanceRequirements,
|
|---|
| 2 | FlightId, FlightNumber, DepartureAirport, DepartureTerminal, DepartureGate,
|
|---|
| 3 | ArrivalAirport, ArrivalTerminal, ArrivalGate, DepartureTime, ArrivalTime, FlightStatus, ReservationId, TicketId,
|
|---|
| 4 | BasePrice, FinalPrice, TicketStatus, SeatId, SeatNumber, SeatLetter, ExitRow, SeatClass) as
|
|---|
| 5 | select passenger.id, passenger.name, passenger.surname, passenger.dateofbirth,
|
|---|
| 6 | passenger.email, passenger.assistancerequirements,
|
|---|
| 7 | flight.id, flight.flightnumber, dAirport.code, dTerminal.name,
|
|---|
| 8 | dGate.number, aAirport.code, aTerminal.name, aGate.number,
|
|---|
| 9 | flight.departure, flight.arrival, flightstatus.name, reservation.id,
|
|---|
| 10 | ticket.id, ticket.baseprice,
|
|---|
| 11 | ticket.finalprice, ticketstatus.name,
|
|---|
| 12 | seat.id, seatconfiguration.number, seatconfiguration.letter,
|
|---|
| 13 | seatconfiguration.exitrow,
|
|---|
| 14 | seatclass.name
|
|---|
| 15 | from ticket
|
|---|
| 16 | join passenger on passenger.id = ticket.passengerid
|
|---|
| 17 | join reservation on ticket.reservationid = reservation.id
|
|---|
| 18 | join flight on ticket.flightid = flight.id
|
|---|
| 19 | left join seat on ticket.seatid = seat.id
|
|---|
| 20 | left join seatconfiguration on seat.seatconfigurationid = seatconfiguration.id
|
|---|
| 21 | left join seatclass on seatconfiguration.seatclassid = seatclass.id
|
|---|
| 22 | join gate as dGate on dGate.id = flight.actualgatedepartureid
|
|---|
| 23 | join terminal as dTerminal on dTerminal.id = dGate.terminalid
|
|---|
| 24 | join airport as dAirport on dAirport.id = dTerminal.airportid
|
|---|
| 25 | join gate as aGate on aGate.id = flight.actualgatearrivalid
|
|---|
| 26 | join terminal as aTerminal on aTerminal.id = aGate.terminalid
|
|---|
| 27 | join airport as aAirport on aAirport.id = aTerminal.airportid
|
|---|
| 28 | join flightstatus on flight.statusid = flightstatus.id
|
|---|
| 29 | join ticketstatus on ticket.ticketstatusid = ticketstatus.id;
|
|---|
