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]
