# establish and connect to database

# SQLite example with table joins

require 'sqlite3'
  db = SQLite3::Database.new( "data.db" )

# Create tables, clearing out any old one first

setups = [
  'drop table if exists aisles',
  'drop table if exists products',
  'create table aisles(aid int primary key not null, title text)',
  'create table products(pid int primary key not null, aid int, name text, colour text)']
for action in setups
  db.execute action
end

# Add in some (test) data to those tables

  seeds = [
        'insert into aisles values (1,"Vegetables")',
        'insert into aisles values (2,"Sweeties")',
        'insert into aisles values (3,"Petfood")',

        'insert into products values (101,1,"Carrots","Orange")',
        'insert into products values (102,1,"Sprouts","Green")',
        'insert into products values (103,2,"Foxmints","Clear")',
        'insert into products values (104,6,"Pizza","Red")']

  for seed in seeds
        db.execute seed
  end

# Tell me what's in the tables

puts "\nSelecting from both tables - ALL possible records"
db.execute( "select * from aisles,products" ) do |row|
    p row
end

puts "\nSelecting from both tables, joined on a common field"
db.execute( "select * from aisles join products on aisles.aid = products.aid" ) do |row|
    p row
end

puts "\nSelecting from both tables, joined on a common field and including orphan records"
db.execute( "select * from aisles left join products on aisles.aid = products.aid" ) do |row|
    p row
end

puts "\nSelecting from both tables, joined on a common field, ONLY orphan records"
db.execute( "select * from aisles left join products on aisles.aid = products.aid where PID is NULL" ) do |row|
    p row
end

  db.close

__END__

Sample output:

trainee@brugges:~/ruby$ ruby tag2

Selecting from both tables - ALL possible records
["1", "Vegetables", "101", "1", "Carrots", "Orange"]
["1", "Vegetables", "102", "1", "Sprouts", "Green"]
["1", "Vegetables", "103", "2", "Foxmints", "Clear"]
["1", "Vegetables", "104", "6", "Pizza", "Red"]
["2", "Sweeties", "101", "1", "Carrots", "Orange"]
["2", "Sweeties", "102", "1", "Sprouts", "Green"]
["2", "Sweeties", "103", "2", "Foxmints", "Clear"]
["2", "Sweeties", "104", "6", "Pizza", "Red"]
["3", "Petfood", "101", "1", "Carrots", "Orange"]
["3", "Petfood", "102", "1", "Sprouts", "Green"]
["3", "Petfood", "103", "2", "Foxmints", "Clear"]
["3", "Petfood", "104", "6", "Pizza", "Red"]

Selecting from both tables, joined on a common field
["1", "Vegetables", "101", "1", "Carrots", "Orange"]
["1", "Vegetables", "102", "1", "Sprouts", "Green"]
["2", "Sweeties", "103", "2", "Foxmints", "Clear"]

Selecting from both tables, joined on a common field and including orphan records
["1", "Vegetables", "101", "1", "Carrots", "Orange"]
["1", "Vegetables", "102", "1", "Sprouts", "Green"]
["2", "Sweeties", "103", "2", "Foxmints", "Clear"]
["3", "Petfood", nil, nil, nil, nil]

Selecting from both tables, joined on a common field, ONLY orphan records
["3", "Petfood", nil, nil, nil, nil]
trainee@brugges:~/ruby$