Examples using Inner Join

Consider an airline baggage tracking application. For every flight ticket number, there is a passenger and their baggage associated with it. The root table is ticket, and it has 2 child tables passengerInfo and baggageInfo. The passengerInfo table contains the details of the passenger and the baggageInfo contains details of the bags checked in by the passenger. These bags are tracked through their transit through multiple intermediary stations. This tracking information is captured in a table called flightlegs which is the child of the baggageInfo table.

Download the script parentchildtbls_loaddata.sql and run it as shown below. This script creates the tables used in the example and loads data into the tables.

• Start your KVSTORE or KVLite

  java -jar lib/kvstore.jar kvlite -secure-config disable

• Open the SQL shell

  java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore

  The SQL prompt appears.
• Load the DDL file to create the necessary tables used in the example

  load -file parentchild.ddl

• Use the load command to run the script. The data from the JSON files is loaded into the tables.

  load -file parentchildtbls_loaddata.sql

The parentchildtbls_loaddata.sql contains the following:

### Begin Script ###
load -file parentchild.ddl
import -table ticket -file ticket.json
import -table ticket.bagInfo -file bagInfo.json
import -table ticket.passengerInfo -file passengerInfo.json
import -table ticket.bagInfo.flightLegs -file flightLegs.json
### End Script ###

Following are the tables created:

• ticket

  
      ticketNo LONG
      confNo STRING
      PRIMARY KEY(ticketNo)

• ticket.bagInfo

      
      id LONG
      tagNum LONG
      routing STRING
      lastActionCode STRING
      lastActionDesc STRING
      lastSeenStation STRING
      lastSeenTimeGmt TIMESTAMP(4)
      bagArrivalDate TIMESTAMP(4)
      PRIMARY KEY(id)

• ticket.bagInfo.flightLegs

  
      flightNo STRING
      flightDate TIMESTAMP(4)
      fltRouteSrc STRING
      fltRouteDest STRING
      estimatedArrival TIMESTAMP(4)
      actions JSON
      PRIMARY KEY(flightNo)

• ticket.passengerInfo

      
      contactPhone STRING
      fullName STRING
      gender STRING
      PRIMARY KEY(contactPhone)