I'm trying to do something in SQL (MySQL) which I'm not sure is possible. The scenario is to do with user surveys, I'm trying to create a survey where the questions are re-asked after a curtain time period. My SQL looks something like this today (I've had to hardcode the question id in the inner select statement because I don't know how else).
SELECT surveyquestions.priority, surveyquestions.id, surveyquestions.question, surveyquestions.html FROM surveyquestions
LEFT JOIN surveyanswers ON surveyquestions.id = surveyanswers.id AND surveyanswers.uuid = 22946252362612764
WHERE (surveyanswers.id IS null OR
(SELECT datestamp FROM surveyanswers WHERE id = 22 ORDER BY datestamp DESC LIMIT 1) <
DATE_SUB(NOW(),INTERVAL 1 HOUR))
AND surveyquestions.priority != 999
AND surveyquestions.brid = 'testprod2'
ORDER BY surveyquestions.priority LIMIT 1;
What I'd like to do it, somehow, use the surveyquestion.id in the outer SELECT as part of the inner select where statement (see WHERE id = 22 is the hard-coded bit).
EDIT: some more info/background to the statement
- priority is the order of which the questions appear
- priority 999 means do not display the question
- brid is the brand/product the question is related to
- uuid is a unique identifier for that the user that's answering the question
In order to do this when the table name is the same, you need to give aliases to the tables:
SELECT surveyquestions.priority, surveyquestions.id, surveyquestions.question, surveyquestions.html FROM surveyquestions
LEFT JOIN surveyanswers oa ON surveyquestions.id = oa.id AND oa.uuid = 22946252362612764
WHERE (surveyanswers.id IS null OR
(SELECT datestamp FROM surveyanswers ia WHERE ia.id = oa.id ORDER BY datestamp DESC LIMIT 1) <
DATE_SUB(NOW(),INTERVAL 1 HOUR))
AND surveyquestions.priority != 999
AND surveyquestions.brid = 'testprod2'
ORDER BY surveyquestions.priority LIMIT 1;
I used oa to denote "outer answers" and ia to denote "inner answers".