• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1140
  • Last Modified:

MS Access Syntax Error - Join expression not supported

This is the query;

Select rs_type from roster left outer join roster_legs on (rs_bookno = rl_bookno and rl_leg = rs_leg) where rl_travelid = 12345 and rs_confirmed is not null and (rs_type = 'Passenger' or rs_type = 'Roundtrip' or rs_type = 'Freight') order by rs_bookno, rs_id

Before people ask me what the data model is - that is irrelevant! I need to know whether the SYNTAX is correct. The record 12345 DOES exist in the database. rl_* fields belongs to 'roster_legs' table and rs_* belongs to 'roster' table
0
tfsln
Asked:
tfsln
  • 4
  • 3
  • 3
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
test this

Select rs_type from roster left  join roster_legs on (rs_bookno = rl_bookno and rl_leg = rs_leg) where rl_travelid = 12345 and rs_confirmed is not null and rs_type In('Passenger' , 'Roundtrip' ,'Freight') order by rs_bookno, rs_id
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
The syntax looks correct - reformatted:

SELECT rs_type

FROM roster
    LEFT OUTER JOIN roster_legs
        ON (rs_bookno
                = rl_bookno
        AND rl_leg
                = rs_leg)

WHERE rl_travelid
                = 12345
        AND rs_confirmed is not null
        AND (rs_type
                = 'Passenger'
        OR rs_type
                = 'Roundtrip'
        OR rs_type
                = 'Freight')

ORDER BY rs_bookno,
    rs_id

But you might need the table names like so:

SELECT Table1.*


FROM Table1
    LEFT OUTER JOIN Table2
        ON Table1.ID
                = Table2.ID
        AND Table1.FIELD1
                = Table2.FIELD1;

mx
0
 
brandonvmooreCommented:
"left outer" is the same thing as just "left".  And I don't think Access recognizes both terms, I think Access only recognizes "left".
0
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.

 
tfslnAuthor Commented:
Nope, still get the same error message...
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"And I don't think Access recognizes both terms"

Actually, it does.  See what I posted ... right from the Access query grid.  However, if you save that SQL ... Access removes the word OUTER.  Full OUTER joins are not supported.  A Left / Right Join is assumed to be and OUTER join.

mx
0
 
tfslnAuthor Commented:

Ahh yes. In the join parenthesis i had to explicitly refer to the tables like this;

Select rs_type from roster left outer join roster_legs on (roster.rs_bookno = roster_legs.rl_bookno and roster_legs.rl_leg = roster.rs_leg) where rl_travelid = 12345 and rs_confirmed is not null and (rs_type = 'Passenger' or rs_type = 'Roundtrip' or rs_type = 'Freight') order by rs_bookno, rs_id
The above query runs fine with the table names added. It wasnt necessary to add the table names to the fields outside the join parenthesis

0
 
tfslnAuthor Commented:
You seem to know your stuff. Maybe you can help me with my other question. Its a simple question, but im getting frustrated with trying to explain it to the guy trying to answer it!

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24323015.html
0
 
brandonvmooreCommented:
You should only 'have' to specify the table name if the field is ambiguous (meaning, the same field name is used in multiple tables).   Not that it's a bad idea to be in the practice of it though.
0
 
brandonvmooreCommented:
mx, If it recognizes "LEFT OUTER" then why does it replace it with "LEFT" ;)
0
 
tfslnAuthor Commented:
> You should only 'have' to specify the table name if the field is ambiguous (meaning, the same field name is used in multiple tables).   Not that it's a bad idea to be in the practice of it though.

That has proven to be untrue, because each of our tables is prefixed with a unique 2 letter abbreviation... rs for the roster table and rl for the roster legs. Even without ambiguous names, it still required me to specify the table names and that was the problem with my query.

What you explained is true for sql server, but not access as it turns out.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"mx, If it recognizes "LEFT OUTER" then why does it replace it with "LEFT" ;)'

Create two tables Table1 and Table2   with Fields ID and FIELD1

Open a new query in SQL view and paste this in:

FROM Table1
    LEFT OUTER JOIN Table2
        ON Table1.ID
                = Table2.ID
        AND Table1.FIELD1
                = Table2.FIELD1;

Then open the query in design view ... no errors.  Maybe we should say Access 'allows' it.

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now