Jump to content


Photo

NOT IN with WHERE clause in subselect


  • Please log in to reply
2 replies to this topic

#1 down_spiral69

down_spiral69
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 20 December 2003 - 09:01 AM

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.

#2 down_spiral69

down_spiral69
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 20 December 2003 - 09:57 AM

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!

#3 DaMaAn

DaMaAn
  • Members
  • Pip
  • Newbie
  • 2 posts
  • LocationLink√∂ping, Sweden

Posted 26 December 2003 - 03:22 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users