Sign in to follow this  
eSCHEn

[web] SQL error with subquery and IN/NOT IN [solved]

Recommended Posts

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              a
users relation:
id{pk}   firstName   lastName
 1         foo         bar
 2         bob         bob
 3         fred        fred
SQL query:
SELECT id, firstName, lastName
FROM users
WHERE id
NOT IN (
SELECT user_id AS id
FROM department_editors
WHERE 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]

Share this post


Link to post
Share on other sites
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].

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this