0

I wanted to write additional query to phpbb3 karma mod which checks that a user can't give karma to same person before he hasn't given karma to 3 other persons. But i'm receiving error in my SQL syntax...

I checked this and this link and query i've written looks as follow

SELECT * 
FROM phpbb_karma AS out 
WHERE out.poster_id = 134
AND out.poster_id
NOT IN 
(SELECT k.karma_id, k.user_id, k.poster_id
FROM phpbb_karma AS k
WHERE user_id = 108
ORDER BY k.karma_id DESC LIMIT 3)

So my inner query gets the 3 last persons (poster_id) whom he (user_id) gave karma. This query alone works but together with my outer query it gives me error

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'out WHERE out.poster_id = 134 AND out.poster_id NOT IN (SELECT k.karma_id, k.u' at line 2

In my outer query i write that the person who will be receiving karma is 134 and that he must not be present in my inner query result.

What could be wrong with that query? Is the error really in second row?

I wrote the same query with LEFT JOIN, but it gives me same syntax error

SELECT *
FROM phpbb_karma AS out
LEFT JOIN phpbb_karma AS in
ON in.poster_id = out.poster_id
WHERE in.poster_id = 134 AND in.poster_id IS NULL

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'out LEFT JOIN phpbb_karma AS in ON in.poster_id = out.poster_id WHERE in.poster_' at line 2

MySQL version is MySQL client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $ but i don't think that the problem is in version, because the LEFT JOIN doesn't work either

Community
  • 1
  • 1
Skyzer
  • 584
  • 3
  • 11
  • 27

4 Answers4

1

Use a different alias, because out is a reserved keyword: http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

Kai Sternad
  • 22,214
  • 7
  • 47
  • 42
1

Apart from using reserved words as the alias you cannot use NOT IN or IN with a sub-select that returns more than one column.

You are basically telling MySQL to compare a single value from poster_id to three different values. Now which one should it take?

Once you get the problem with the alias sorted out, you need to remove two columns from the sub-select.

Based on your naming schema I assume you actually want 

NOT IN (SELECT k.karma_id FROM phpbb_karma ....)

instead.

  • uhh, didnt edit in 5 minutes, so i write as reply that it works now when i omit LIMIT 3 in my inner query otherwise it says ˇThis version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'ˇ – Skyzer Sep 10 '11 at 14:39
0

out is a keyword in MySQL. You can fix this either by naming the alias something else or putting backticks around out.

Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
0

Just put back-ticks around out and in. looks like they are reserved keywords in mysql

Brian Glaz
  • 15,468
  • 4
  • 37
  • 55