SQL Examples

Let us now see a few example SQL queries for inner join:

Example 1: Fetch the details of the passenger with ticket number 1762324912391.

SELECT fullname, contactPhone, gender FROM ticket a,ticket.passengerInfo b WHERE
      a.ticketNo=b.ticketNo AND a.ticketNo=1762324912391

Explanation: This is an example of an inner join where the parent table ticket is joined with its child table passengerInfo and a filter is applied to restrict the result. Note that the shard key here is ticketNo. If the shard key is not explicitly specified while creating the root table, the primary key of the root table is taken as the shard key. This shard key is inherited by all the descendant tables.

Output:

{"fullname":"Elane Lemons","contactPhone":"600-918-8404","gender":"F"}
1 row returned

Example 2: Fetch the bag details of all passengers who have been issued a ticket.

SELECT * FROM ticket a, ticket.bagInfo b WHERE a.ticketNo=b.ticketNo

Explanation: This is an example of an inner join where the parent table ticket is joined with its child table bagInfo.

Output:


{"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},"b":{"ticketNo":1762324912391,"id":79039899168383,"tagNum":1765780623244,"routing":"MXP/CDG/SLC/BZN","lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"BZN","lastSeenTimeGmt":"2019-03-15T10:13:00.0000Z","bagArrivalDate":"2019-03-15T10:13:00.0000Z"}}
{"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},"b":{"ticketNo":1762355527825,"id":79039899197492,"tagNum":17657806232501,"routing":"BZN/SEA/CDG/MXP","lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MXP","lastSeenTimeGmt":"2019-03-22T10:17:00.0000Z","bagArrivalDate":"2019-03-22T10:17:00.0000Z"}}
{"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},"b":{"ticketNo":1762344493810,"id":79039899165297,"tagNum":17657806255240,"routing":"MIA/LAX/MEL","lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MEL","lastSeenTimeGmt":"2019-02-01T16:13:00.0000Z","bagArrivalDate":"2019-02-01T16:13:00.0000Z"}}
{"a":{"ticketNo":1762376407826,"confNo":"ZG8Z5N"},"b":{"ticketNo":1762376407826,"id":7903989918469,"tagNum":17657806240229,"routing":"JFK/MAD","lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MAD","lastSeenTimeGmt":"2019-03-07T13:51:00.0000Z","bagArrivalDate":"2019-03-07T13:51:00.0000Z"}}
{"a":{"ticketNo":1762392135540,"confNo":"DN3I4Q"},"b":{"ticketNo":1762392135540,"id":79039899156435,"tagNum":17657806224224,"routing":"GRU/ORD/SEA","lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"SEA","lastSeenTimeGmt":"2019-02-15T21:21:00.0000Z","bagArrivalDate":"2019-02-15T21:21:00.0000Z"}}
5 rows returned

Example 3: Fetch the flight leg details of the bags of the passenger with ticket number 1762344493810.

SELECT * FROM ticket a, ticket.bagInfo.flightLegs b WHERE a.ticketNo=b.ticketNo AND
      a.ticketNo=1762344493810

Explanation: : This is an example of an inner join where the parent table ticket is joined with its descendant flightlegs. A descendant table can be any level hierarchically below a table (For example flightLegs is the child of bagInfo which is the child of ticket, so flightLegs is a descendant of ticket). The result is then filtered for a particular ticket number.

Output:

{"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},"b":{"ticketNo":1762344493810,"id":79039899165297,"flightNo":"BM604","flightDate":"2019-02-01T06:00:00.0000Z","fltRouteSrc":"MIA","fltRouteDest":"LAX","estimatedArrival":"2019-02-01T11:00:00.0000Z","actions":[{"actionAt":"MIA","actionCode":"ONLOAD to LAX","actionTime":"2019-02-01T06:13:00Z"},{"actionAt":"MIA","actionCode":"BagTag Scan at MIA","actionTime":"2019-02-01T05:47:00Z"},{"actionAt":"MIA","actionCode":"Checkin at MIA","actionTime":"2019-02-01T04:38:00Z"}]}}
{"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},"b":{"ticketNo":1762344493810,"id":79039899165297,"flightNo":"BM667","flightDate":"2019-02-01T06:13:00.0000Z","fltRouteSrc":"LAX","fltRouteDest":"MEL","estimatedArrival":"2019-02-01T16:15:00.0000Z","actions":[{"actionAt":"MEL","actionCode":"Offload to Carousel at MEL","actionTime":"2019-02-01T16:15:00Z"},{"actionAt":"LAX","actionCode":"ONLOAD to MEL","actionTime":"2019-02-01T15:35:00Z"},{"actionAt":"LAX","actionCode":"OFFLOAD from LAX","actionTime":"2019-02-01T15:18:00Z"}]}}
2 rows returned

Example 4: Find the number of hops for all the bags of a passenger with ticket number 1762355527825 . If there are multiple bags checked in for a passenger, then the number of hops for all the bags are displayed.

SELECT b.id,count(*) AS NUMBER_HOPS FROM ticket a, ticket.bagInfo.flightLegs b WHERE a.ticketNo=b.ticketNo AND a.ticketNo=1762355527825 GROUP BY
    b.id

Explanation: Here, you group the data based on the bag id (using GROUP BY) and get the count of flight legs (using count()) for every bag. Additionally, you filter the results for a particular ticket number.

Output:

{"id":79039899197492,"NUMBER_HOPS":3}
1 row returned

Example 5: 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.

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: Fetch the name of the passenger, the last seen station of whose bag 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 7: 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"}

Such a join between tables that are not in an ancestor-descendant relationship is not possible with Left Outer Join and NESTED TABLES.