Questions without any answer from an user

user_12544391 0
Viewed 27

I have a questions table, a user table and an answers table with user_id and question_id as foreign keys.

I tried:

select questions.title from questions
full outer join answers on answers.question_id = questions.id
where answers.user_id = 1
group by questions.id
having count(answers.id) = 0

But the issue is that when questions.id is null it groups by null

Ideally the query should be doable with ActiveRecord (Ruby on Rails).

ruby-on-rails sql
1 Answers

Use LEFT JOIN if you want questions without an answer:

select q.title
from questions q left join
     answers a
     on a.question_id = q.id and a.user_id = 1
where a.question_id is null;

Some notes:

  • Aggregation is not needed.
  • You are looking for non-matches. Using a LEFT JOIN, just check if there are no non-matches.
  • FULL JOIN with a WHERE clause is really somewhat complicated to explain. But essentially you are getting all answers for user_id = 1 along with questions for all other users.
user_11440350
Related