Ask Asded for help in rewriting a code snippet

[manking]

I ask for help in rewriting a code snippet yesterday for MYSQL from Access and Discovered I pasted the wrong snippet here is the right one Could some help in rewriting this for mysql??

select a.Unique_ID, a.Date_Creation, a.createdBy_UserName, a.replies, a.views, a.lastPost_UserName, a.subject, a.Date_LastPost, a.locked, a.announcement, a.lastPost_UserID, a.createdBy_UserID, a.movedFrom, null as lastVisit, (select top 1 answer from Gerobase_Forum_Messages c where c.answer = true and c.topic_ID = a.Unique_ID) as answered from Gerobase_Forum_Topics a where a.forum_ID = 24 order by a.announcement, a.Date_LastPost desc


I have changed c.answer = true to c.answer = 1 (and 0) with no success

the error message is

Syntax error or access violation: You have an error in your SQL syntax near 'select top 1 answer from Gerobase_Forum_Messages c where c.answer = true and c.t' at line 5

 

[feherke]

Hi

The [tt]top[/tt] is MSSQL specific. Use [tt]limit[/tt].

[b]select[/b] a.Unique_ID, a.Date_Creation, a.createdBy_UserName, a.replies, a.views, a.lastPost_UserName, a.subject, a.Date_LastPost, a.locked, a.announcement, a.lastPost_UserID, a.createdBy_UserID, a.movedFrom, [b]null as[/b] lastVisit, (
  [b]select[/b] answer
  [b]from[/b] Gerobase_Forum_Messages c
  [b]where[/b] c.answer = true [b]and[/b] c.topic_ID = a.Unique_ID
  [red][b]limit[/b] 1[/red]
) [b]as[/b] answered
[b]from[/b] Gerobase_Forum_Topics a
[b]where[/b] a.forum_ID = 24
[b]order by[/b] a.announcement, a.Date_LastPost [b]desc[/b]

Troels Arvin's SQL comparison is a nice document about such differences.

Feherke.

http://rootshell.be/~feherke/

 

[r937]

you're not going to have a lot of luck until you upgrade to at least version 4.1 to support the use of subqueries

r937.com | rudy.ca