SQLite LEFT JOIN & WHERE Clause confusion

Question

I have two Tables:

Table 1: Questions:

QuestionId NUMERIC
Title      TEXT

Test Data in Question Table:

QuestionId     Title
1              What is your name?
2              What is your age?

Table 2: Answers:

AnswerId     NUMERIC
PersonId     NUMERIC
QuestionId   NUMERIC
Answer       TEXT

If there is NO DATA in Answers Table then below query returns correct results (2 rows):

SELECT  q.QuestionId, q.Title, a.Answer
FROM    Questions q
    LEFT OUTER JOIN Answers a ON q.QuestionId = a.QuestionId
WHERE    a.PersonId = 2 OR a.PersonId IS null

But if i have for example 1 record in Answer Table like:

AnswerId     1
PersonId     1
QuestionId   1
Answer       'My Name is Yaqub'

Below query works fine:

SELECT  q.QuestionId, q.Title, a.Answer
FROM    Questions q
    LEFT OUTER JOIN Answers a ON q.QuestionId = a.QuestionId
WHERE    a.PersonId = 1 OR a.PersonId IS null

But this query returns just one row:

SELECT  q.QuestionId, q.Title, a.Answer
FROM    Questions q
    LEFT OUTER JOIN Answers a ON q.QuestionId = a.QuestionId
WHERE    a.PersonId = 2 OR a.PersonId IS null

The above query returns just one row which is wrong, becuase i have two questions in Question Table & the above query should return both of them because of the condition 'OR a.PersonId IS null'.

Why its not returning TWO Rows?

Desired Results:

I want to get all the questions(2 rows) & those answers where PersonId has some value,for example TWO rows for Person 2 because i have no data for him in the Answer Table.

Answer 1

I FOUND the SOLUTION:

SELECT q.QuestionId, q.Title, a.Answer
FROM Questions q
 LEFT OUTER JOIN Answers a ON q.QuestionId = a.QuestionId AND 
(a.PersonId = 2 OR a.PersonId IS null)

This query will list all the questions & the WHERE clause will filter out those answers where PersonID has a specific value or is null. So whenever there's an answer to a question, i'll get no NULL values for PersonID.

Answer 2

You can do it just like this:

SELECT q.QuestionId, q.Title, a.Answer
FROM Questions q
 LEFT OUTER JOIN Answers a ON q.QuestionId = a.QuestionId AND 
a.PersonId = 2

The query still returns records with PersonId = null because of outer join.

Answer 3

This is what you have when you do the join, before the where is applied:

QuestionID    AnswerID    PersonID
1             1           1
2             null        null

There actually is only one record in the questions table that matches (PersonID = 2 or PersonID is null), so you should expect only one row.

Did you mean to put

a.PersonId = 1 OR a.PersonId IS null

instead?