SELECT Questions.*, COUNT(UserCode) AS VoteCount, ; SUM( IIF(UserCode = lcUserCode, 1, 0)) AS VoteCountCurentUser ; FROM Questions ; LEFT JOIN Votes ; WHERE Questions.SurveyPK=Votes.SurveyPK ; ORDER BY CloseDate DESC ; GROUP BY SurveyPK ; INTO CURSOR TQuery>I'm doing a survey module and want to run a single query that gives me the total number of votes in a survey (easy with COUNT and GROUP BY), PLUS whether the logged in user voted in each one or not.
SELECT Questions.*, UserCode, COUNT(UserCode) AS VoteCount ; > FROM Questions ; > LEFT JOIN Votes ; > WHERE Questions.SurveyPK=Votes.SurveyPK ; > ORDER BY CloseDate DESC ; > GROUP BY SurveyPK ; > INTO CURSOR TQuery>