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)