Example: Using Inner Join

Let us now see a few examples of inner join on the tables used in the airline baggage tracking application.

Example 6-123 Fetch the ticket number, passenger name, and bag details of all the passengers

SELECT a.ticketNo, b.fullName, c.bagArrivalDate FROM ticket a, ticket.passengerInfo b,
      ticket.bagInfo c WHERE a.ticketNo = b.ticketNo AND b.ticketNo=c.ticketNo

Explanation: This is an example of an inner join of three tables, that is, the parent table ticket, and the sibling tables passengerInfo and bagInfo. The query returns the ticket number and the arrival date of the bags of all the passengers.

Output:

{"ticketNo":1762324912391,"fullName":"Elane Lemons","bagArrivalDate":"2019-03-15T10:13:00.0000Z"}
{"ticketNo":1762355527825,"fullName":"Doris Martin","bagArrivalDate":"2019-03-22T10:17:00.0000Z"}
{"ticketNo":1762344493810,"fullName":"Adam Phillips","bagArrivalDate":"2019-02-01T16:13:00.0000Z"}
{"ticketNo":1762392135540,"fullName":"Adelaide Willard","bagArrivalDate":"2019-02-15T21:21:00.0000Z"}
{"ticketNo":1762376407826,"fullName":"Dierdre Amador","bagArrivalDate":"2019-03-07T13:51:00.0000Z"}

5 rows returned

Example 6-124 Fetch the name of the passenger whose bag’s last seen station is “MEL”

SELECT a.fullName FROM ticket.passengerInfo a, ticket.bagInfo b WHERE a.ticketNo = b.ticketNo AND b.lastSeenStation = "MEL"

Explanation: This is an example of an inner join of the sibling tables passengerInfo and bagInfo. The name of the passenger whose bag was last seen at the "MEL" station is returned.

Output:

{"fullName":"Adam Phillips"}

1 row returned

Example 6-125 Fetch the name of the passenger whose flight route destination is "MEL"

SELECT a.fullName FROM ticket.passengerInfo a, ticket.bagInfo.flightlegs b WHERE a.ticketNo =
      b.ticketNo AND b.fltRouteDest = "MEL"

Explanation: This is an inner join of two tables, passengerInfo and flightlegs, that are not in an ancestor-descendant relationship.

Output:

{"fullName":"Adam Phillips"}

1 row returned