SQL Syntax and SQL Server Version Error

Question

i have problem with this code:

$comments->query = "SELECT " . PREFIX . "_comments.id, post_id, " . PREFIX . "_comments.user_id, " . PREFIX . "_comments.date, " . PREFIX . "_comments.autor as gast_name, " . PREFIX . "_comments.email as gast_email, text, ip, is_register, name, " . USERPREFIX . "_users.email, news_num, " . USERPREFIX . "_users.comm_num, user_group, lastdate, reg_date, signature, foto, fullname, land, yahoo, " . USERPREFIX . "_users.xfields, " . PREFIX . "_post.title, " . PREFIX . "_post.date as newsdate, " . PREFIX . "_post.alt_name, " . PREFIX . "_post.category FROM " . PREFIX . "_comments LEFT JOIN " . PREFIX . "_post ON " . PREFIX . "_comments.post_id=" . PREFIX . "_post.id LEFT JOIN " . USERPREFIX . "_users ON " . PREFIX . "_comments.user_id=" . USERPREFIX . "_users.user_id " . $where . " ORDER BY id desc";

Error:

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 'ON dle_comments.post_id=dle_post.id LEFT JOIN dle_users ON dle_comments.user_id=' at line 1

Edit:

SELECT 
  dle_comments.id, post_id, 
  dle_comments.user_id, 
  dle_comments.date, 
  dle_comments.autor as gast_name, 
  dle_comments.email as gast_email, 
  text, ip, is_register, 
  group_concat(mid) as `awards`, 
  name, dle_users.email, news_num, 
  dle_users.comm_num, user_group, 
  lastdate, reg_date, signature, 
  foto, fullname, land, icq, 
  dle_users.xfields, dle_post.title, 
  dle_post.date as newsdate, dle_post.alt_name, dle_post.category 
FROM 
  dle_comments 
  LEFT JOIN dle_awards 
    ON uid = dle_post 
    ON dle_comments.post_id=dle_post.id 
  LEFT JOIN dle_users 
    ON dle_comments.user_id=dle_users.user_id 
ORDER BY id desc 
LIMIT 0,30

My SQL Version: 5.5.20

how i can fix this problem?

can you post an echo or print of the query? With all the PREFIX etc it is almost unreadable... — arnoudhgz
– arnoudhgz, Commented Aug 6, 2012 at 20:54
please put some line breaks in your query.... I tried to edit it, but I don't see my changes. — arnoudhgz
– arnoudhgz, Commented Aug 6, 2012 at 21:05

Chris Trahey · Accepted Answer · 2012-08-06 21:05:04Z

1

You have an ON clause immediately following another ON clause. Based on the tables/columns specified in the second one, it looks like you are missing a JOIN to dle_post in there:

-- existing:
LEFT JOIN dle_awards ON uid = dle_post ON dle_comments.post_id=dle_post.id 
-- becomes:
LEFT JOIN dle_awards ON uid = dle_post LEFT JOIN dle_post ON dle_comments.post_id=dle_post.id

Of course, that probably needs tweaking since it doesn't look like dle_post (in the first ON clause) is actually valid. I'd need to see the schema to know.

answered Aug 6, 2012 at 21:05

Chris Trahey

18.3k1 gold badge44 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ali Over a year ago

thank u, but after i did what ever u said, still i have that problem! i dont know or maybe i cant understand your tips.

user1542136 · Accepted Answer · 2012-08-06 21:05:26Z

1

... ON uid = dle_post LEFT JOIN ON dle_comments.post_id=dle_post.id ...

Add the above LEFT JOIN in between the two 'ON' clauses

answered Aug 6, 2012 at 21:05

user1542136

31 bronze badge

Comments

user1577291 · Accepted Answer · 2012-08-06 21:28:26Z

The error in your query was expected. You have redundant ON in your query where the error is pointing.

Just look where the error is:

SELECT dle_comments.id, post_id, dle_comments.user_id, dle_comments.date, dle_comments.autor as gast_name, dle_comments.email as gast_email, text, ip, is_register, group_concat(mid) as awards, name, dle_users.email, news_num, dle_users.comm_num, user_group, lastdate, reg_date, signature, foto, fullname, land, icq, dle_users.xfields, dle_post.title, dle_post.date as newsdate, dle_post.alt_name, dle_post.category FROM
dle_comments LEFT JOIN dle_awards
ON uid = dle_post ON dle_comments.post_id=dle_post.id
LEFT JOIN dle_users ON dle_comments.user_id=dle_users.user_id ORDER BY id desc LIMIT 0,30

=============
When doing junction see the right syntax ... from TABLE1 LEFT JOIN TABLE2 ON TABLE1.columnName=TABLE2.columnName. So remove one in the two consecutive ON in italic block and specify one column from dle_comments and another from dle_awards to use in the remaining ON part.

Collectives™ on Stack Overflow

SQL Syntax and SQL Server Version Error

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related