sqlite3: how to select same row twice when there is a match? -

create sof.sql

create table "android_metadata" ("locale" text default 'en_us'); insert "android_metadata" values ('en_us');  create table main.t_def (  _id     integer primary key autoincrement not null,  word      text(20) not null,  word_def  text(20) not null );  insert t_def (word, word_def) values ('ball','spherical object'); insert t_def (word, word_def) values ('cat','feline'); insert t_def (word, word_def) values ('dog','common housekept');  create table main.t_a (   _id        integer primary key autoincrement not null,   corr_answer      text(20) not null,   user_answer      text(20) not null,   is_correct       integer not null );  insert t_a (user_answer, corr_answer, is_correct) values ('ball','cat',0); insert t_a (user_answer, corr_answer, is_correct) values ('dog','dog',1); .exit 

then run:

sqlite3 foo.db < sof.sql 

i want result set is:

 ball|spherical object|cat|feline|0 

this closest have gotten:

 select t_def.word, t_def.word_def t_def, t_a t_a.is_correct=0 , t_a.corr_answer=t_def.word; 

to values 2 rows, need 2 instances of table:

select t_a.user_answer,        user_def.word_def user_word_def,        t_a.corr_answer,        corr_def.word_def corr_word_def,        t_a.is_correct t_a join t_def user_def on t_a.user_answer = user_def.word join t_def corr_def on t_a.corr_answer = corr_def.word not t_a.is_correct