Changing the join order
Posted on 2008-10-28
I'm developing a front end query utility that let's users build queries off of a model I define. I have it creating the query fine except for one catch, the join order is sometimes out of order and the query won't run (if I move one of the joins up two lines, it works fine). I obviously know the order I should put them in when I manually write it but I never know the combintion of tables a user will pick. Question:
Is there a way to make SQL Server re-order my joins for me?
Oddly enough, if I write my SQL with old non ANSI joins, the SQL Server does choose the join order and the query works fine. I would much prefer to use ANSI joins as opposed to (select * from table1, table2, table3 where table1.id = table2.id and table2.otherfield = table3.otherfield).