| 1 | create view CargoDetails (FlightId, FlightNumber, Origin, Destination, CargoID, CargoType, WeightKg, Height, Width, Length,
|
|---|
| 2 | Description) as
|
|---|
| 3 | select flight.id, flight.flightnumber, da.code, aa.code,
|
|---|
| 4 | baggage.id, 'LUGGAGE',
|
|---|
| 5 | baggage.weight, baggage.height, baggage.width, baggage.length,
|
|---|
| 6 | 'Passenger Luggage'
|
|---|
| 7 | from baggage
|
|---|
| 8 | join ticket on baggage.ticketid = ticket.id
|
|---|
| 9 | join flight on ticket.flightid = flight.id
|
|---|
| 10 | join gate dg on dg.id = flight.actualgatedepartureid
|
|---|
| 11 | join terminal dt on dt.id = dg.terminalid
|
|---|
| 12 | join airport da on da.id = dt.airportid
|
|---|
| 13 | join gate ag on ag.id = flight.actualgatearrivalid
|
|---|
| 14 | join terminal at_ on at_.id = ag.terminalid
|
|---|
| 15 | join airport aa on aa.id = at_.airportid
|
|---|
| 16 |
|
|---|
| 17 | union all
|
|---|
| 18 |
|
|---|
| 19 | select flight.id, flight.flightnumber, da.code, aa.code,
|
|---|
| 20 | additionalcargo.id, 'ADDITIONAL CARGO',
|
|---|
| 21 | additionalcargo.weight, additionalcargo.height, additionalcargo.width,
|
|---|
| 22 | additionalcargo.length, additionalcargo.description
|
|---|
| 23 | from additionalcargo
|
|---|
| 24 | join flight on additionalcargo.flightid = flight.id
|
|---|
| 25 | join gate dg on dg.id = flight.actualgatedepartureid
|
|---|
| 26 | join terminal dt on dt.id = dg.terminalid
|
|---|
| 27 | join airport da on da.id = dt.airportid
|
|---|
| 28 | join gate ag on ag.id = flight.actualgatearrivalid
|
|---|
| 29 | join terminal at_ on at_.id = ag.terminalid
|
|---|
| 30 | join airport aa on aa.id = at_.airportid;
|
|---|
| 31 |
|
|---|
