[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

Problem joining 3 tables

When I try joining 3 tables, I get the following message;

Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'm.mstDeptId = d.dptId LEFT OUTER JOIN branch b ON m.mstBranchId = b.brcId'.  

My query looks like this;

 SELECT m.mstFname, m.mstLname, {fn LEFT(m.mstLname, 1)} AS Initial, m.mstPhone, d.dptName, b.brcName
    FROM master m
        LEFT OUTER JOIN department d
              ON m.mstDeptId = d.dptId
        LEFT OUTER JOIN branch b
              ON m.mstBranchId = b.brcId
  ORDER BY m.mstLname

What am I doing wrong? I'm currently using Coldusion MX 6.1.

Thanks,

Errol Farro
0
Errol Farro
Asked:
Errol Farro
  • 2
1 Solution
 
pinaldaveCommented:
hello,
This may sound stupid but whenever I have this problem I do this....and works for me... please try and let me tknow...

SELECT m.mstFname, m.mstLname, {fn LEFT(m.mstLname, 1)} AS Initial, m.mstPhone, d.dptName, b.brcName
    FROM master m
       LEFT OUTER JOIN department d
            ON (m.mstDeptId = d.dptId)
       LEFT OUTER JOIN branch b
            ON (m.mstBranchId = b.brcId)
  ORDER BY m.mstLname
0
 
Errol FarroAuthor Commented:
No, I already tried it as you are recommending but I get the same error message.
0
 
mrichmonCommented:
In access you usually need to put parenthesis around multiple joins so it knows what order to do the joins.

Try this:
 SELECT m.mstFname, m.mstLname, {fn LEFT(m.mstLname, 1)} AS Initial, m.mstPhone, d.dptName, b.brcName
    FROM ((master m
       LEFT OUTER JOIN department d
            ON m.mstDeptId = d.dptId)
       LEFT OUTER JOIN branch b
            ON m.mstBranchId = b.brcId)
  ORDER BY m.mstLname

0
 
Errol FarroAuthor Commented:
Thanks a million. This has solved my problem.
Best Regards,

Errol Farro
Aruba
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now