CODEWITHSUNDEEP
mysqljoin
Dexter
Dexter

Reputation: 9314

Get all rows from table1 and not exists in table2 with multiple where clause

I have a online test where I have two tables t1 (lvl_addition) question table and t2 (student_answers) answer table. t1 table has 100 sums.

Student has to answer the sum one by one and every answer will saves in the t2 table using the fk of t1

t1
-----
id
test_FK
modepaper_FK
sum1
sum2
...
answ (original answer)

t2
-----
id
t1_FK
student_FK
modelpaper_FK
answ (student answer)

modelpaper_FK is referred to the modelpaper table id, and student_FK is for the student_profiles id.

The exam is time based, if the time ends before they finish all 100 sums. The exam stops and the t2 table will have only the answered sums.

What iam looking for

i need to output all the 100 sums from the t1 table (even if doesn't exists in t2). Iam using NOT EXIST clause but the problem is i also need to use WHERE clause to refer t1.id = n AND t2.student_FK = n

How can i do that.

The code below gets me only the answered sums from t2.

SELECT t1.* 
FROM lvl_additions t1 
LEFT JOIN student_answers t2 ON t1.id = t2.lvladditions_FK
WHERE t1.modelpaper_FK = 59 AND t2.studentprofile_FK = 279

Upvotes: 0

Views: 39

Answers (1)

Gosfly
Gosfly

Reputation: 1300

As Sebastian said, the problem comes from the following condition :

t2.studentprofile_FK = 279

What you have to do is to move this condition and any other condition on t2 right inside the left join 'on' clause. See the query below :

SELECT t1.*, t2.*
FROM t1
LEFT JOIN t2 ON (t1.id = t2.t1_fk AND t2.student_id = 2)

And the most important : SEE COMPLETE DEMO HERE

EDIT after discussion in chat :

The OP needs to get every questions/answers to a modelpaper for a specific student, even if there is not an answer to every question in the modelpaper. In this case, a text will be displayed in place of the null answer.

  1. There are more than 4500 questions
  2. Questions are "grouped by" modelpaper, each modelpaper contains 100 questions
  3. A student may have answered to every questions in a modelpaper

Based on these information, the following query did the job :

SELECT
     t1.*,
     COALESCE(t2.answ, 'replacement text')
FROM lvl_additions t1
LEFT JOIN student_answers t2 ON (t1.id = t2.lvladditions_FK AND t2.studentprofile_FK = 279)
WHERE t1.modelpaper_fk = 59

EDIT 2 :

There are 2 cases to differenciate :

  1. Student runs out of time, answer is null and ctime is null (ctime is a column in t2 that records duration)
  2. Student skips a question, answer is null but ctime is 00:00:00

The following query allows us to display a different text for each case :

SELECT
     t1.*,
     IF(t2.ctime IS NULL, 'time runs out', IF(t2.ctime = 0, 'question skipped', t2.answ)) AS answer
FROM lvl_additions t1
LEFT JOIN student_answers t2 ON (t1.id = t2.lvladditions_FK AND t2.studentprofile_FK = 279)
WHERE t1.modelpaper_fk = 59

Upvotes: 1

Related Questions