The answeritem table looks like this:
id (integer)
answer_id (integer)
question_id (integer) -> references the question for this answer
value (varchar)
Each survey has multiple questions. One of the questions is a score from 0 to 10. one of the other questions is text area. We now want to show all the answers that were entered for this text area for persons that gave a score of 10
So I use the following sql:
- Code: Select all
SELECT ai.value, CAST(score.value AS INT) AS SCORE
FROM answer a
LEFT JOIN survey s ON a.survey_id = s.survey_id
LEFT JOIN answeritem score ON a.answer_id = score.answer_id AND s.score_question = score.question_id -- This join is used to get the correct score record in the answeritems table
LEFT JOIN answeritem ai ON a.answer_id = ai.answer_id AND ai.question_id = s.text_question -- we only want to see the answers for a specific question, the id for this question is stored in the surveys table (each survey has a diffent question ID for the text area question)
WHERE CAST(SCORE.return_value AS INT) = 10)
As soon as I add the WHERE clause to my sql it fails with an error: Invalid input syntax for integer: "some text value".
The text value is in the answeritem table, but not in records that should be available for the SCORE join.
Using google I found a way around this by using the following WHERE clause:
- Code: Select all
WHERE SCORE.return_value ~ '^\d+$' AND CAST(SCORE.return_value AS INT) = 10)
My questions are:
1. Why does the original WHERE clause not work? Isn't the join specific enough to only filter the records in answeritem that contain score values?
2. Is this fix the best solution for this problem, or is there a better solution I can use for this?
The problem is related to my joins, because when I modify the second join into:
LEFT JOIN answeritem score ON a.answer_id = score.answer_id AND score.question_id = 11 -- Only show answers for 1 specific survey (where the score question has id 11)
Then the error also gone. I just don't understand why using the original join results into a different dataset.
I hope my explanation is clear enough