Recode values

[axtg86]

So here's the deal:

 

I have a question and answer table. The question table specifies whether a question is formulated positively or negatively (qsReversed is 0 or 1). Based on this answer I want to sum the answer values accordingly. If it is not reversed 5=5, if it is reversed 5 should be 1, 4->2, etc. I know exactly how to recode variables in SPSS, but haven't been able to do so using SQL.

 

Closest that I have gotten (no error, no results) is

 

SELECT * FROM questions eq

LEFT JOIN answers ea ON eq.qsID=ea.qsID

WHERE eq.qsReversed=1

AND ea.answerValue IS NOT NULL

AND ea.answerValue = CASE

WHEN '5' THEN '1'

WHEN '4' THEN '2'

WHEN '2' THEN '4'

WHEN '1' THEN '5'

ELSE '3' END;

 

Thanks for any input anyone might have!

[fenway]

You're not too far off -- but you want that CASE expression in your select column list, not your WHERE clause.  And the LEFT JOIN with IS NOT NULL is really just an INNER JOIN.