Solved

Problem joining 3 tables

Posted on 2004-10-25
163 Views
Last Modified: 2013-12-24
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
Question by:Errol Farro
    4 Comments
     
    LVL 22

    Expert Comment

    by:pinaldave
    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
     

    Author Comment

    by:Errol Farro
    No, I already tried it as you are recommending but I get the same error message.
    0
     
    LVL 35

    Accepted Solution

    by:
    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
     

    Author Comment

    by:Errol Farro
    Thanks a million. This has solved my problem.
    Best Regards,

    Errol Farro
    Aruba
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Cisco Complete Network Certification Training

    If you’re an IT engineer or technician, it's time you take your career to the next level. This elite training bundle is brimming with all of the information you need to learn to sit for Cisco CNNA, CCNP, and CCENT certification exams.

    A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
    Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now