mysql - query which creates missing rows based on anther table -

i have many forms users fill out. each form contains list of questions. in first table form id , id's of questions.

form_id   | question_id 1         | 1 1         | 2 1         | 3 2         | 4 2         | 5 

this table has 2 forms 1 has 3 questions , other 2. have second table has answers users have given questions.

user_id   | form_id | question_id | answer 476       | 1       | 1           | "answer1" 476       | 1       | 3           | "answer2" 693       | 1       | 1           | "answer3" 693       | 1       | 2           | "answer4" 235       | 2       | 5           | "answer5" 

in example, 2 users have filled out form 1 , 1 user has filled in form 2. none have filled in questions. possible write query combines 2 tables , give me answers user have given including questions didn't answer? i'd results this.

user_id   | form_id | question_id | answer 476       | 1       | 1           | "answer1" 476       | 1       | 2           | null 476       | 1       | 3           | "answer2" 693       | 1       | 1           | "answer3" 693       | 1       | 2           | "answer4" 693       | 1       | 3           | null 235       | 2       | 4           | null 235       | 2       | 5           | "answer5" 

the problem have when use left join this

select * template t left join answers on a.template_id = t.template_id , a.question_id = t.question_id , t.template_id = t.template_id; 

is row results missing user_id.

yes, specified result can returned query.

one way achieve join inline view, , "outer join" operation second table.

the "trick" getting distinct list of user_id , form_id second table, using query, example:

select user_id, form_id   second_table   group user_id, form_id 

and using query inline view (wrapping in parens, assigning table alias, , referencing table in outer query.

all that's required after "outer join" second table.

for example:

select r.user_id      , q.form_id       , q.question_id      , a.answer   first_table q   join ( select p.user_id, p.form_id            second_table p           group p.user_id, p.form_id        ) r     on r.form_id = q.form_id   left   join second_table     on a.user_id = r.user_id    , a.form_id = r.form_id    , a.question_id = q.question_id  order     r.user_id      , q.form_id       , q.question_id 

note keyword "left" specifies outer join operation, returning rows left side, along matching rows right side. typical "inner" join exclude rows didn't find matching row table on right side.