Since you have a one-to-one relationship I'm not sure why you don't just put the inseam with the Employee, but perhaps you're just giving an example here.
I would do it this way which is going to run a heck of lot faster than using string compares as you are doing. pragma foreign_keys = on; create table Employee (id integer primary key, name); create table Uniform (employeeid integer primary key, inseam, foreign key(employeeid) references employee(id)); insert into employee values (1,"Joe"); insert into employee values (2,"Steve"); insert into employee values (3,"Eric"); insert into employee values (4,"Dave"); insert into Uniform values ((select id from employee where name="Joe"), 77); insert into Uniform values ((select id from employee where name="Dave"), 81); select Name from Employee left outer join Uniform on Employee.id=Uniform.employeeid; explain query plan select Name from Employee join Uniform on Employee.id=Uniform.employeeid; sqlite> explain query plan select Name from Employee join Uniform on Employee.id=Uniform.employeeid; 0|0|0|SCAN TABLE Employee (~1000000 rows) 0|1|1|SEARCH TABLE Uniform USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) Or do this which I think does exactliy what you are thinking... sqlite> select name from Employee where id in (select employeeid from Uniform); Joe Dave sqlite> explain query plan select name from Employee where id in (select employeeid from Uniform); 0|0|0|SEARCH TABLE Employee USING INTEGER PRIMARY KEY (rowid=?) (~25 rows) Or this more complete example including inseam: sqlite> select name,inseam from Employee,Uniform where id in (select employeeid from Uniform) and Employee.id=Uniform.Employeeid; Joe|77 Dave|81 sqlite> explain query plan select name,inseam from Employee,Uniform where id in (select employeeid from Uniform) and Employee.id=Uniform.Employeeid; 0|0|0|SEARCH TABLE Employee USING INTEGER PRIMARY KEY (rowid=?) (~25 rows) 0|1|1|SEARCH TABLE Uniform USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
