cross join

package require TclOO
package require sqlite3
package require tdbc::sqlite3

##
## CREATE DATABASES
##
::tdbc::sqlite3::connection create db [file normalize [file join . db.sqlite3]]

##
## JOINS
##
puts ""
set stmtHandle [db prepare {
	CREATE TABLE department (
		DepartmentID int UNIQUE,
		DepartmentName varchar(25)
	);
	CREATE TABLE employee (
		LastName varchar(25),
		DepartmentID int,
		FOREIGN KEY (DepartmentID) REFERENCES department(DepartmentID)
	);
}]
${stmtHandle} execute
set recs {
	{31 Sales}
	{33 Engineering}
	{34 Clerical}
	{35 Marketing}
}
set stmtHandle [db prepare {INSERT INTO department VALUES(:id,:name)}]
foreach rec ${recs} {
	lassign ${rec} id name
	${stmtHandle} execute 
}
set recs {
	{Rafferty 31}
	{Jones 33}
	{Steinberg 33}
	{Robinson 34}
	{Smith 34}
	{John NULL}
}
set stmtHandle [db prepare {INSERT INTO employee VALUES(:name,:id)}]
foreach rec ${recs} {
	lassign ${rec} name id
	${stmtHandle} execute 
}
puts "- employee -"
puts [join [db allrows -as lists -- {SELECT * FROM employee}] \n]
puts "- department -"
puts [join [db allrows -as lists -- {SELECT * FROM department}] \n]
puts "- inner join -"
puts [join [db allrows -as lists -- {
	SELECT employee.LastName, employee.DepartmentID, department.DepartmentName, department.DepartmentID
	FROM employee INNER JOIN department
	ON employee.DepartmentID=department.DepartmentID
}] \n]
puts "- cross join -"
puts [join [db allrows -as lists -- {
	SELECT employee.LastName, employee.DepartmentID, department.DepartmentName, department.DepartmentID
	FROM   employee JOIN department;
}] \n]

##
## DELETE TABLES
##
set stmtHandle [db prepare {
	DROP TABLE t1;
	DROP TABLE t2;
}]

##
## RESULT
##
if 0 {
[tom]$ tclsh main2.tcl 

- employee -
Rafferty 31
Jones 33
Steinberg 33
Robinson 34
Smith 34
John NULL
- department -
31 Sales
33 Engineering
34 Clerical
35 Marketing
- inner join -
Rafferty 31 Sales 31
Jones 33 Engineering 33
Steinberg 33 Engineering 33
Robinson 34 Clerical 34
Smith 34 Clerical 34
- cross join -
Rafferty 31 Sales 31
Rafferty 33 Engineering 33
Rafferty 34 Clerical 34
Rafferty 35 Marketing 35
Jones 31 Sales 31
Jones 33 Engineering 33
Jones 34 Clerical 34
Jones 35 Marketing 35
Steinberg 31 Sales 31
Steinberg 33 Engineering 33
Steinberg 34 Clerical 34
Steinberg 35 Marketing 35
Robinson 31 Sales 31
Robinson 33 Engineering 33
Robinson 34 Clerical 34
Robinson 35 Marketing 35
Smith 31 Sales 31
Smith 33 Engineering 33
Smith 34 Clerical 34
Smith 35 Marketing 35
John 31 Sales 31
John 33 Engineering 33
John 34 Clerical 34
John 35 Marketing 35
[tom]$ 

}