NOT IN with WHERE clause in subselect
Posted 20 December 2003 - 09:01 AM
I\'m in the process of converting phpSurvey to MySQL, and I\'m nearly finished now. My main problem is subselects, or rather, how to replace them. Here\'s a query that I have to fix:
SELECT * FROM respondent WHERE respondentID NOT IN (SELECT respondentID FROM assign WHERE surveyID = $surveyID) - with $surveyID being a variable.
After consulting the MySQL manual and phpFreaks, here\'s my first guess at a replacement query:
SELECT respondent.* FROM respondent LEFT JOIN assign ON respondent.respondentID = assign.respondentID WHERE assign.surveyID = $surveyID AND assign.respondentID IS NULL
However, the where clause is impossible. If anyone has any suggestions, please let me know.
Posted 20 December 2003 - 09:57 AM
SELECT * FROM survey S
WHERE 1 AND S.status != -1 AND S.status != 0
AND (S.owner = \'root\' OR S.surveyID IN ( SELECT surveyID FROM access WHERE username = \'root\' AND ( permission = 2 OR permission = 3 OR permission = 6 OR permission = 7 ) ) )
I don\'t even know where to start with this one!
Posted 26 December 2003 - 03:22 PM
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users