Access Left Join problem

  • Thread starter Thread starter James Down
  • Start date Start date
J

James Down

I have an SQL statement that works in SQL server 2000 but I get a "Join
expression not supported" error when trying it in Access. I think it's got
something to do with the fact I'm selecting from multiple tables and
performing a left join, but I'm not sure.

Any ideas how I can adapt the statement below to work in access?

Cheers for any help given

JD

SELECT
NavNodes_Table.pageID,
PageContent_Table.elementContent,
ContentTypes_Table.contentType,
PageElements_Table.elementName,
PageFiles_Table.fileWidth,
PageFiles_Table.fileHeight,
PageFiles_Table.fileAlt,
PageFiles_Table.fileIDName
FROM
NavNodes_Table,
ContentTypes_Table,
PageElements_Table,
PageContent_Table LEFT JOIN PageFiles_Table
ON
PageContent_Table.contentID = PageFiles_Table.contentID
WHERE
NavNodes_Table.pageID = 1 and
NavNodes_Table.pageID = PageContent_Table.pageID and
PageElements_Table.elementID = PageContent_Table.elementID and
PageElements_Table.contentTypeID = ContentTypes_Table.contentTypeID
 
Hello, James!
You wrote in message on Thu, 01 Jul 2004 12:39:24 +0100:

JD> Any ideas how I can adapt the statement below to work in access?

Try to rewrite your query as:

SELECT
NavNodes_Table.pageID,
PageContent_Table.elementContent,
ContentTypes_Table.contentType,
PageElements_Table.elementName,
PageFiles_Table.fileWidth,
PageFiles_Table.fileHeight,
PageFiles_Table.fileAlt,
PageFiles_Table.fileIDName
FROM (((NavNodes_Table INNER JOIN NavNodes_Table ON NavNodes_Table.pageID =
PageContent_Table.pageID)
INNER JOIN PageElements_Table ON PageContent_Table.elementID =
PageElements_Table.elementID)
INNER JOIN ContentTypes_Table ON PageElements_Table.contentTypeID =
ContentTypes_Table.contentTypeID)
LEFT JOIN PageFiles_Table ON PageContent_Table.contentID =
PageFiles_Table.contentID
WHERE
NavNodes_Table.pageID = 1

With best regards, Igor.
ICQ: 111469481
 
Hi Igor,

Thanks so much for that. It did the trick, just one amend to your statement
needed. You said:
FROM (((NavNodes_Table INNER JOIN NavNodes_Table ON NavNodes_Table.pageID =
Click to expand...
PageContent_Table.pageID)

But needed to change it to:

FROM (((NavNodes_Table INNER JOIN PageContent_Table ON NavNodes_Table.pageID
= PageContent_Table.pageID)

Other than that works a treat!

Thanks

JD
 
Hello, James!
You wrote in message on Thu, 01 Jul 2004 14:24:47 +0100:

JD> Thanks so much for that.

You are welcome.

??>> FROM (((NavNodes_Table INNER JOIN NavNodes_Table ON
??>> NavNodes_Table.pageID =
JD> PageContent_Table.pageID)
JD> But needed to change it to:
JD> FROM (((NavNodes_Table INNER JOIN PageContent_Table ON
JD> NavNodes_Table.pageID = PageContent_Table.pageID)

Sorry. Of course you are right. I have corrected the sql statement in the
Notepad and not tested result properly.

With best regards, Igor.
ICQ: 111469481
 
Back
Top