nGdAnaR1

1drop table if exists users;
2create table users
3(
4user_id int unsigned not null auto_increment primary key,
5username varchar(32) not null
6)engine=innodb;
7
8drop table if exists question;
9create table question
10(
11question_id int unsigned not null auto_increment primary key,
12ques varchar(255) not null
13)engine=innodb;
14
15drop table if exists question_ans;
16create table question_ans
17(
18user_id int unsigned not null,
19question_id int unsigned not null,
20ans varchar(255) not null,
21primary key (user_id, question_id)
22)engine=innodb;
23
24insert into users (username) values 
25('user1'),('user2'),('user3'),('user4');
26
27insert into question (ques) values 
28('question1 ?'),('question2 ?'),('question3 ?');
29
30insert into question_ans (user_id,question_id,ans) values
31(1,1,'foo'), (1,2,'mysql'), (1,3,'php'),
32(2,1,'bar'), (2,2,'oracle'),
33(3,1,'foobar');
34
35select
36 u.*,
37 q.*,
38 a.ans
39from users u
40cross join question q
41left outer join question_ans a on a.user_id = u.user_id and a.question_id = q.question_id
42order by
43 u.user_id,
44 q.question_id;
45
46select
47 u.*,
48 q.*,
49 a.ans
50from users u
51cross join question q
52left outer join question_ans a on a.user_id = u.user_id and a.question_id = q.question_id
53where
54 u.user_id = 2
55order by
56 q.question_id;