Jump to content

Archived

This topic is now archived and is closed to further replies.

down_spiral69

NOT IN with WHERE clause in subselect

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.