I want to return unique question but even with DISTINCT if I have one question that has multiple answers all of the same question with the answers get return I only want that question return one time here is my sql
SELECT
DISTINCT questions.id AS question_id,
questions.title AS question_title,
questions.created_at AS questionCreatedAt,
questions.updated_at AS question_updated_at,
answers.id AS answer_id,
answers.content AS answer_content,
answers.created_at AS answer_created_at,
answers.updated_at AS answer_updated_at,
(SELECT SUM(votes.value) AS votes FROM votes WHERE answers.id =votes.answer_id)
FROM questions
LEFT JOIN answers ON questions.id = answers.question_id
LEFT JOIN votes ON answers.id = votes.answer_id;
You should use "DISTINCT ON" instead of "DISTINCT".
SELECT
DISTINCT ON (questions.id) questions.id,
questions.title AS question_title,
questions.created_at AS questionCreatedAt,
questions.updated_at AS question_updated_at,
answers.id AS answer_id,
answers.content AS answer_content,
answers.created_at AS answer_created_at,
answers.updated_at AS answer_updated_at,
(SELECT SUM(votes.value) AS votes FROM votes WHERE answers.id =votes.answer_id)
FROM questions
LEFT JOIN answers ON questions.id = answers.question_id
LEFT JOIN votes ON answers.id = votes.answer_id;