Pat Pat - 4 months ago 9
SQL Question

Oracle SQL query to return 1 or 0 based on if value exists

I have 2 table Question and Answer tables.
Question table has

ID Question_Text
1 Question1
2 Question2
3 Question3


Answer table

Question_ID Answer_text Answered_by
1 Ans1 User1
1 Ans2 User2
1 Ans3 User3
2 Ans4 User1


I need to find out which all questions were answered already?
Desired outcome

ID Question_Text Answered
1 Question1 true/1
2 Question2 true/1
3 Question3 false/0


I have written the below query to find whether the question is answered by using EXISTS. I need answered value true/false and also want to fine tune this query so that it checks for first occurrence in answer table and get the value as true/false, instead of running through the thousands of value in answer table.

SELECT *
FROM QUESTIONS A
WHERE EXISTS (SELECT *
FROM ANSWERS B
WHERE A.QUESTION_ID = B.QUESTION_ID);

Answer

A simple LEFT JOIN will do:

SELECT  Q.*, 
        CASE WHEN A.Question_ID IS NULL THEN 0 ELSE 1 END Answered
FROM Question Q
LEFT JOIN ( SELECT DISTINCT Question_ID
            FROM Answer) A
    ON Q.ID = A.Question_ID;