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)