Converting MSSQL to Mysql

[clintw]

I'm trying to convert a MSSQL select statement to MySQL, but am having difficulty with the following section:

...
INNER JOIN tableA ON tableB.field1 = tableA.field1
INNER JOIN tableC ON tableA.field2 = tableC.field2 
ON tableD.field4 = tableB.field4
ON tableE.field5 = tableC.field5 
ON tableF.field6 = tableB.field6
ORDER BY tableD.field7 DESC
LIMIT 0,100
 

Result:
#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 'ON tableF.field6 = tableB.field6 ORDER BY'

The MSSQL code works perfectly in MSSQL, but MySQL doesn't like the syntax of the last section. Is there anyone who would be willing to assist?

I'm using Mysql 5.0.38.

Thanks in advance

[r035198x]

I'm trying to convert a MSSQL select statement to MySQL, but am having difficulty with the following section:

...
INNER JOIN tableA ON tableB.field1 = tableA.field1
INNER JOIN tableC ON tableA.field2 = tableC.field2 
ON tableD.field4 = tableB.field4
ON tableE.field5 = tableC.field5 
ON tableF.field6 = tableB.field6
ORDER BY tableD.field.7 DESC
LIMIT 0,100
 

Result:
#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 'ON tableF.field6 = tableB.field6 ORDER BY'

The MSSQL code works perfectly in MSSQL, but MySQL doesn't like the syntax of the last section. Is there anyone who would be willing to assist?

I'm using Mysql 5.0.38.

Thanks in advance

Do you have it as ORDER BY tableD.field.7 or ORDER BY tableD.field7 ?

[clintw]

Do you have it as ORDER BY tableD.field.7 or ORDER BY tableD.field7 ?

That was a typo - sorry. It should read "ORDER BY tableD.field7 DESC". I've rectified it in my original post.

[r035198x]

I'm trying to convert a MSSQL select statement to MySQL, but am having difficulty with the following section:

...
INNER JOIN tableA ON tableB.field1 = tableA.field1
INNER JOIN tableC ON tableA.field2 = tableC.field2 
ON tableD.field4 = tableB.field4
ON tableE.field5 = tableC.field5 
ON tableF.field6 = tableB.field6
ORDER BY tableD.field7 DESC
LIMIT 0,100
 

Result:
#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 'ON tableF.field6 = tableB.field6 ORDER BY'

The MSSQL code works perfectly in MSSQL, but MySQL doesn't like the syntax of the last section. Is there anyone who would be willing to assist?

I'm using Mysql 5.0.38.

Thanks in advance

Try it as

 INNER JOIN tableA ON tableB.field1 = tableA.field1
INNER JOIN tableC ON tableA.field2 = tableC.field2 
ON (tableD.field4 = tableB.field4
  and tableE.field5 = tableC.field5 
  and tableF.field6 = tableB.field6)
ORDER BY tableD.field7 DESC
LIMIT 0,100

and post any error messages you get

[clintw]

I've added the brackets and replaced the ON with AND, but the error remains much the same:

#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 'ORDER BY tableD.field7 DESC
LIMIT 0,' at line 36

I think I've tried this already, based on what I read on mysql.com and the correct syntax for JOINS.

[r035198x]

I've added the brackets and replaced the ON with AND, but the error remains much the same:

#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 'ORDER BY tableD.field7 DESC
LIMIT 0,' at line 36

I think I've tried this already, based on what I read on mysql.com and the correct syntax for JOINS.

There is still another ON that needs to be replaced by AND in the code that I gave you.

[r035198x]

There is still another ON that needs to be replaced by AND in the code that I gave you.

Just looking on you query again. Which table are you selecting from?
Shouldn't you have it as

from tableName innner join tableA on (tableName.field = tableA.field and tableName.anotherField = tableA.anotherField and e.t.c)
innner join tableB on (tableName.field = tableB.field)
inner joing tableC on (tableName.field = tableC.field)
e.t.c 
 

[clintw]

Selection is made FROM tableF. Will try rewriting this statement from scratch, the MSSQL was done by someone else.

Just looking on you query again. Which table are you selecting from?
Shouldn't you have it as

from tableName innner join tableA on (tableName.field = tableA.field and tableName.anotherField = tableA.anotherField and e.t.c)
innner join tableB on (tableName.field = tableB.field)
inner joing tableC on (tableName.field = tableC.field)
e.t.c