Syntax error in Join

johnmontgomery

New member
Local time
Yesterday, 22:41
Joined
Feb 29, 2016
Messages
5
I am writing a query and attempting to use an inner join in a subquery that I am using for a column value. However,
Access says the following has a syntax error.
Select PATRON_GROUP.PATRON_GROUP_NAME from
(PATRON_GROUP Inner Join PATRON_BARCODE on
PATRON_GROUP.PATRON_GROUP_ID = PATRON_BARCODE.PATRON_GROUP_ID) inner join
PATRON_BARCODE ON PATRON_BARCODE.PATRON_ID = PATRON.PATRON_ID

Alas, I don't see it, and have been trying things for several hours today. Help please!
 
Please tell readers WHAT you are trying to accomplish.
You are telling us HOW you did something that isn't working.

Is it possible you are missing a WHERE clause to indicate criteria???
Why the Joins?
 
it's why I avoid underscores - although 'legal' it can make it difficult to see the error. Also better to avoid all uppercase for the same reason.

You have the patron_barcode table in twice - should only be once - and if needs to be twice then one of them needs to be aliased

Why not just use the query builder? would have avoided all these issues
 
Actually, I thought I was using a code from a query that is already in use here. However, now I have moved on, using your help--thanks. The next piece, which is the error I started my day with is in the FROM clause at the bottom of this big query. The first where clause in the FROM clause (where PA1.ADDRESS_TYPE = '1') results in this message: JOIN expression not supported.
select PATRON.TITLE,
PATRON.FIRST_NAME,
PATRON.MIDDLE_NAME,
PATRON.LAST_NAME,
' ',
' ',
' ',
' ',
'5692',
PATRON.BARCODE,
' ',
' ',
(Select PATRON_GROUP.PATRON_GROUP_NAME from
(PATRON_GROUP Inner Join PATRON_BARCODE on
PATRON_GROUP.PATRON_GROUP_ID = PATRON_BARCODE.PATRON_GROUP_ID) inner join
PATRON ON PATRON_BARCODE.PATRON_ID =
PATRON.PATRON_ID) AS borrowerCategory,
format(PURGE_DATE ,'YYYY-MM-DD'),
(Select location_Code from LOCATION inner join

patron on

PATRON.home_location=location.location_id) AS

regionalLib,
PA1.ADDRESS_LINE1,
PA1.ADDRESS_LINE2,
PA1.CITY,
PA1.STATE_PROVINCE,
PA1.ZIP_POSTAL,
PA1.COUNTRY,
(select PATRON_PHONE.PHONE_NUMBER
FROM ((((PATRON LEFT JOIN

VOYAGER_DATABASES ON PATRON.DB_ID =

VOYAGER_DATABASES.DB_ID) INNER JOIN

PATRON_ADDRESS ON PATRON.PATRON_ID =

PATRON_ADDRESS.PATRON_ID) INNER JOIN

PATRON_BARCODE ON PATRON.PATRON_ID =

PATRON_BARCODE.PATRON_ID) INNER JOIN

PATRON_GROUP ON

PATRON_BARCODE.PATRON_GROUP_ID =

PATRON_GROUP.PATRON_GROUP_ID) INNER

JOIN PATRON_PHONE ON

PATRON_ADDRESS.ADDRESS_ID =

PATRON_PHONE.ADDRESS_ID) AS patronPhone,
' ',
PA2.ADDRESS_LINE1,
PA2.ADDRESS_LINE2,
PA2.CITY,
PA2.STATE_PROVINCE,
PA2.ZIP_POSTAL,
PA2.COUNTRY,
PA3.ADDRESS_LINE1


FROM
(( Patron P
INNER JOIN Patron_Address PA1 ON
PA1.patron_id = P.patron_id AND
PA1.address_type = '1')
LEFT OUTER JOIN Patron_Address PA2 ON
PA2.patron_id = P.patron_id AND
PA2.address_type = '2')
LEFT OUTER JOIN Patron_Address PA3 ON
PA3.patron_id = P.patron_id AND
PA3.address_type = '3';

What I am trying to accomplish is to provide information from 1-3 rows in the PATRON_ADDRESS table and they are distinguished by the address type. So I need to get each row for every PATRON_ID in the PATRON tale. There will always be an ADDRESS_TYPE '1' record, but the others might not be there.
 
already answered in your thread about the problem - and don't you think the error message is telling you something useful?
 
I'll resist the temptation to sarcasm, and simply say, "Yes, I think the error message is telling me something." Depending upon the tool I'm using that parses, pre-compiles, and generates a query plan, which is different from COBOL with DB2, which is different from C with Sybase X (I'm dating myself, I know), which is different from Oracle in Java, which is slightly different from Oracle in PL/SQL, which is slightly different form Oracle in Aqua Data Studio v.13, an error in an SQL statement to the effect that the pre-compiler can't figure out what you are doing with the tokens passed by the parser (at least that is how it worked in DB2/MVS when I owned the database commands, database access, and part of the commit code as a database kernel engineer). However, whether the actual error is the highlighted bit of code, or something that came before it is the real problem, is hard to guess when I don't know if I have all the () and and such in just the right place for the dialect of SQL that I am using. My experience using joins is not as complete as some, obviously (when I first learned SQL, I didn't use the word JOIN but did need to be sure that the implicit joins I asked for were not Cartesian), and having used multiple dialects of SQL it is not always easy to know what is or is not legal. MS Access is totally foreign territory to me, and after watching about 25 videos on Youtube in a series someone made of 52 videos on Access, I gave up on that route.

On the other point, I see a Query Wizard, but not a Query Builder in Access 2007. I looked at the descriptions for the four types of queries that it can make, and none of those sounded like what I need to do.
1. Extract data from many different tables, and
2. Put that into a tab-delimited file, for which
3. the vendor supplied an Excel spreadsheet that needs to be saved as tab-delimited.

I have looked at several examples and not found a good example or a syntax chart for an Access join with criteria, and read a thread on Stack Overflow that suggested using a where clause and an ON condition to filter rows is approximately the same thing. Since I know most of the possibilities for SQL, it is simpler fr me to go to the SQL View in Access and write code.
 

Users who are viewing this thread

Back
Top Bottom