CODEWITHSUNDEEP
mysqljoinleft-join
user1324762
user1324762

Reputation: 805

Left join returns zero rows

I have two tables:

question
question_id | question

question_answer
answer_id | question_id | choice_id | user_id | explain

I want to get all questions for which particular user haven't answered yet.

SELECT question, question_id as questionId 
FROM question q 
LEFT JOIN question_answer qa USING(question_id)
WHERE qa.user_id!=$userId

I get in this case zero rows. I tried also 

SELECT question, q.question_id as questionId 
FROM question q 
LEFT JOIN question_answer qa ON q.question_id=qa.question_id AND qa.user_id!=$userId

Obviously this returns all records. I stuck here how to make this query.

Upvotes: 2

Views: 124

Answers (4)

Tariq M Nasim
Tariq M Nasim

Reputation: 1278

If you are not bound to use JOIN operation then you can try this as you

want to get all questions for which particular user haven't answered yet:

select * from question
where
    question_id not in (select distinct question_id from question_answer where user_id=$userId);

See the demo on Sqlfiddle

Don't know whether not in is slower or faster than JOIN operation.

Upvotes: 2

Drew
Drew

Reputation: 24959

i would modify the answer from MG above to be simply 

SELECT question, q.question_id as questionId 
FROM question q 
LEFT JOIN question_answer qa ON q.question_id = qa.question_id 
and qa.user_id=2
WHERE qa.user_id IS NULL;

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try this instead:

SELECT question, q.question_id as questionId 
FROM question q 
LEFT JOIN question_answer qa ON q.question_id = qa.question_id 
WHERE qa.user_id IS NULL;

SQL Fiddle Demo

This will give you questions with no answers yet.

However, if you want to get users that have not answered questions yet, you should have an extra JOIN to the users table. Something like:

SELECT u.id, u.name
FROM  users u 
LEFT JOIN question_answer qa ON qa.user_id = u.id
WHERE qa.question_id IS NULL;

Updated SQL Fiddle Demo

Upvotes: 1

Ja͢ck
Ja͢ck

Reputation: 173562

The part of your LEFT JOIN where you check for a specific user should be reversed, i.e. user_id = $userId instead of user_id <> $userId.

From the resulting rows you select the ones where user_id IS NULL to find the questions for which $userId has not answered (yet).

SELECT question, q.question_id as questionId 
FROM question q 
LEFT JOIN question_answer qa ON q.question_id=qa.question_id AND qa.user_id=$userId
WHERE qa.user_id IS NULL

Upvotes: 2

Related Questions