 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2008Need pointers on joins in queries.
Hi all, Trying to follow the Access help on joins to link 3 tables in an ASP query. The tables are linked on the key field (e.g. R_ID), and work as expected in forms on local machines. Yet, the below example results in a "missing operator" error. mEnq = "SELECT * FROM Table 1 (INNER JOIN Table2 ON Table1.R_ID = Table2.R_ID AND INNER JOIN Table3 ON Table1.R_ID = Table3.R_ID) ORDER BY Table1.R_ID" I expected it to error out for other reasons. ;-) Any help is greatly appreciated. Neil You don't use AND in conjunction with JOIN. I would expect the query should be something like: mEnq = "SELECT * FROM Table 1 INNER JOIN (Table2 INNER JOIN Table3 ON Table1.R_ID = Table3.R_ID) ON Table1.R_ID = Table2.R_ID ORDER BY Table1.R_ID" In general, you're best off building the query through Access's graphical query builder, then looking at the SQL that's generated. (You can get to it through the View menu)  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Hi all, > [quoted text clipped - 12 lines] > > Neil Hi Douglas, Recently, Douglas J. Steele <NOSPAM_djsteele@NOSPAM_canada.com> posted: > You don't use AND in conjunction with JOIN. I would expect the query > should be something like: > > mEnq = "SELECT * FROM Table 1 INNER JOIN (Table2 INNER JOIN Table3 ON > Table1.R_ID = > Table3.R_ID) ON Table1.R_ID = Table2.R_ID ORDER BY Table1.R_ID" Interesting. The placement of your parentheses is a little puzzling, though. Can you explain the placement ahead of "Table2..."? > In general, you're best off building the query through Access's > graphical query builder, then looking at the SQL that's generated. > (You can get to it through the View menu) I'll look into that, as well. Thanks, Neil > Hi Douglas, > [quoted text clipped - 9 lines] > Interesting. The placement of your parentheses is a little puzzling, > though. Can you explain the placement ahead of "Table2..."? Sorry, I can't explain the vaguaries of the Jet SQL dialect. <g> In other dialects, you'd use SELECT * FROM Table 1 INNER JOIN Table2 ON Table1.R_ID = Table2.R_ID INNER JOIN Table3 ON Table1.R_ID = Table3.R_ID ORDER BY Table1.R_ID If I had to guess, I'd say that you've got (Table2 INNER JOIN Table3 ON Table1.R_ID = Table3.R_ID) as a subquery that gets evaluated before the other join gets done, but I'm not sure whether Rushmore (the optimization technology that's built into Jet) isn't smart enough to look and see when it makes more sense to do a different subquery first based on the number of rows in each table. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) Hi Douglas, Recently, Douglas J. Steele <NOSPAM_djsteele@NOSPAM_canada.com> posted: > Sorry, I can't explain the vaguaries of the Jet SQL dialect. <g> In > other dialects, you'd use [quoted text clipped - 4 lines] > INNER JOIN Table3 ON Table1.R_ID = Table3.R_ID > ORDER BY Table1.R_ID That is exactly what I would do in a more conforming SQL dialect. However with Jet SQL, who knows? I'll play with this and see. BTW - I did take your other suggestion and build the query in Access and look at it. My, what a mess, and it didn't work in the ASP code either. > If I had to guess, I'd say that you've got (Table2 INNER JOIN Table3 > ON Table1.R_ID = Table3.R_ID) as a subquery that gets evaluated > before the other join gets done, but I'm not sure whether Rushmore > (the optimization technology that's built into Jet) isn't smart > enough to look and see when it makes more sense to do a different > subquery first based on the number of rows in each table. Well, if I can't get the INNER JOINs to work, I may have to resort to extremely inefficient methods to get the job done. Thanks, again. Neil > Hi Douglas, > [quoted text clipped - 14 lines] > BTW - I did take your other suggestion and build the query in Access and > look at it. My, what a mess, and it didn't work in the ASP code either. That doesn't make sense. If it runs in Access, it should run from ASP (assuming you're using the correct connection string) Did you get an error from ASP? SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) Recently, Douglas J. Steele <NOSPAM_djsteele@NOSPAM_canada.com> posted: >> Hi Douglas, >> [quoted text clipped - 21 lines] > > Did you get an error from ASP? The error was the same "missing operator" one as before. The above fails in the same way (not too surprising, as it is "legitimage" SQL). ;-) I've simplified the query to try tracking down the error. I can get a single INNER JOIN to pass muster, but not the 3-table join. I'm pretty sure it's a syntax problem, rather than a missing operator issue as reported. Best, Neil |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.