   [web] SQL error with subquery and IN/NOT IN [solved]
  Hi there.   I'm having a moment of frustration with a SQL query here and I was wondering if someone would be kind enough to have a look at it and see if anything looks glaringly obvious.department_editors relation:user_id{pk}   dept_url{pk}   1    s   2    s   3    b   3    ausers relation:id{pk}   firstName   lastName 1    foo    bar 2    bob    bob 3    fred   fredSQL query:SELECT id, firstName, lastNameFROM usersWHERE idNOT IN (SELECT user_id AS idFROM department_editorsWHERE dept_url='a')Now to my mind this should work perfectly and return the tuples (1, foo, bar) and (2, bob, bob) from the users relation.  My thinking is that the inner subquery should return (3) only as that's the only user_id that is assigned to the dept_url 'a'.  From this subquery, the only users that are not in it are (1) and (2).Separating the two queries and performing them individually produces the results I'd expect, it's just that joining them together gives me an error when I run it in phpMyAdmin: "MySQL said: #1064 - You have an error in your SQL syntax near 'SELECT user_id AS id  FROM department_editors  WHERE dept_url = 'a'  )LIMIT 0' at line 1".I'm running MySQL 3.23.42 and phpMyAdmin 2.7.0-pl2 if it makes any odds to the situation.Any help much appreciated.[Edited by - eSCHEn on June 11, 2006 2:40:10 AM]
  i'd suggest removing the 'AS id' from your sub queryhope that helps !matchu   ;  Nah, no matter how I mess around with the various bits of the inner and outer queries I get no joy.  I've resorted to some scripting hacks to sort it out though so all is well in the end.  Thanks for having a shot though [smile].   ;  MySQL 3.23 doesn't do subqueries. IIRC you need MySQL 5 for that.   ;  Ahh that's good to know, thanks for that.  Unfortunately this is going on to a client's server and I have no control over what versions of software they use so I'm stuck with what are ancient versions of software: PHP 4.1.2, MySQL 3.23.42 alpha and Apache 1.3.27.   ;  You can politely suggest an upgrade to the client :-) He just might listen/   ;  Yeah I've told them how old and insecure the software they are using is but the site is for a group of five or so universities which is run jointly by a committee, so nothing will ever get done *sigh*.  What's that quotation again, "If computers ever get too powerful we'll just organize them into a committee, that'll fox them".  Never a truer word spoken.   