Migrating form MSSQL to MySQL

[astrodestino]

Hi!
I am migrating from MSSQL to MySQL and I'm having trouble converting the SQL strings.
I got thins:

						''	strSQL = "SELECT top 1" & strDbTable & "Author.Username, " & strDbTable & "Author.Author_ID, " & strDbTable & "Thread.Topic_ID, " & strDbTable & "Thread.Thread_ID, " & strDbTable & "Thread.Message_date "
						''	strSQL = strSQL & "FROM " & strDbTable & "Author, " & strDbTable & "Thread  "
						''	strSQL = strSQL & "WHERE " & strDbTable & "Author.Author_ID = " & strDbTable & "Thread.Author_ID AND " & strDbTable & "Thread.Topic_ID"' IN "
						''	strSQL = strSQL & "	(SELECT top 1" & strDbTable & "Topic.Topic_ID "
						''	strSQL = strSQL & "	FROM " & strDbTable & "Topic "
						''	strSQL = strSQL & "	WHERE " & strDbTable & "Topic.Forum_ID = " & intForumID & " "
						''	strSQL = strSQL & "	ORDER BY " & strDbTable & "Topic.Last_entry_date DESC) "
						''	strSQL = strSQL & "ORDER BY " & strDbTable & "Thread.Message_date DESC;"

That doesnt work so I changed to this:

strSQL="SELECT tblAuthor.Username, tblAuthor.Author_ID, tblThread.Author_ID,tblThread.Topic_ID, tblThread.Thread_ID, tblThread.Message_date from tblAuthor, tblThread  left Outer Join tblTopic on tblTopic.Forum_ID = tblTopic.Forum_ID WHERE " & strDbTable & "Author.Author_ID = " & strDbTable & "Thread.Author_ID AND " & strDbTable & "Thread.Topic_ID"' Limit 1"

But something must be wrong, when I open my website I download all the database.
How can I translate the upper sql string to my sql?
tnx!!!!

 

[SQLDenis]

Should this WHERE " & strDbTable & "Author.Author_ID = " & strDbTable & "Thread.Author_ID AND " & strDbTable & "Thread.Topic_ID"' Limit 1"

be
WHERE Author.Author_ID = " & strDbTable & "AND Thread.Author_ID " & strDbTable & "Thread.Topic_ID =" & SomethingHere & " Limit 1"

There is also a single quote here & "Thread.Topic_ID"'

and what is the condition?


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[astrodestino]

Honestly I dont know.
How can I translate the MSSQL sintax?
This is a forum sintax and it should display forum title, number of threads, number of topics and las author name but I cant get the author name displayed, it will show the first user on the DB (the admin)
Tnx!

 

[TonyGroves]

Can you show us the SQL string that results from the program code?

 

[astrodestino]

This is the original:

SELECT top 1 tblAuthor.Username, tblAuthor.Author_ID, tblThread.Author_ID,tblThread.Topic_ID, tblThread.Thread_ID, tblThread.Message_date FROM tblAuthor, tblThread  WHERE tblAuthor.Author_ID = tblThread.Author_ID AND tblThread.Topic_ID IN (SELECT top 1tblTopic.Topic_ID FROM tblTopic WHERE tblTopic.Forum_ID = " & intForumID & "ORDER BY tblTopic.Last_entry_date DESC) ORDER BY tblThread.Message_date DESC;"

 

[TonyGroves]

TOP 1" is not valid MySQL syntax. Assuming it means "return only the first record", you would replace it with "LIMIT 1" at the end of the query.

 

[astrodestino]

But whi it takes too long to retreive data?
I am working with my Localhost and the database is at my hosting provider and when I execute the site my site begins to download too much from the db before showing data.
Is that normal when you work with a remote db?
Tnx

 

[TonyGroves]

If you use "LIMIT 1" at the end of the query it will only return one record at most. Have you done that?

 

[astrodestino]

I did it

strSQL="SELECT tblAuthor.Username, tblAuthor.Author_ID, tblThread.Author_ID,tblThread.Topic_ID, tblThread.Thread_ID, tblThread.Message_date from tblAuthor, tblThread  left Outer Join tblTopic on tblTopic.Forum_ID = tblTopic.Forum_ID WHERE " & strDbTable & "Author.Author_ID = " & strDbTable & "Thread.Author_ID AND " & strDbTable & "Thread.Topic_ID"' Limit 1"

But it will do the same, it will download tons of tada from the DB