[SQLize]

Home | About | FAQ | Links | Feedback

Enter MySQL script (temp tables/CRUD allowed, 5 sec / 5MB / 20 rows max):

create table Question(QuestionId int, QuestionNumber int);
insert Question values (1,1);

create table Answer(
    QuestionId int, AnswerId int, AnswerContent varchar(50), CorrectAnswer int);
insert Answer values
   (1,             1,           'Leeds',                1),
   (1,             2,           'Manchester',           0),
   (1,             3,           'Birmingham',           0);

create table StudentAnswer(StudentAnswer int, QuestionId int, StudentId int);
insert StudentAnswer values
        (2,                1, 1),
        (3,                1, 2),
        (1,                1, 3);

/* create temp table copy for re-opening table - limitation with temp tables */
create table Answer2 select * from Answer;

SELECT 
    'ABB' as SessionId,
    q.QuestionNumber,
    a.AnswerContent,
    a2.AnswerContent as StudentAnswerContent,
    StudentId
FROM Question q
    INNER JOIN StudentAnswer sa ON q.QuestionId = sa.QuestionId
    JOIN Answer a ON sa.QuestionId = a.QuestionId  
    JOIN Answer2 a2 ON sa.StudentAnswer = a2.AnswerId
WHERE
    (a.CorrectAnswer = '1')

| (permalink: Wk0LXJQ7De) Sample 1 | Sample 2

No results are displayed if 5 second timeout reached.

SessionId	QuestionNumber	AnswerContent	StudentAnswerContent	StudentId
ABB	1	Leeds	Leeds	3
ABB	1	Leeds	Manchester	1
ABB	1	Leeds	Birmingham	2

(3 rows returned)