NOT IN with WHERE clause in subselect

[down_spiral69]

Hi all,

 

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.

[down_spiral69]

Oh, and here\'s another major problem piece of SQL:

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!

[DaMaAn]

Not a complete solution, maybe not even close to an answer, but to reduce complexity use \"permission in (2,3,6,7)\" in the later SQL statement.