Outer joins in MSAccess

[Rayz66]

Hi there.

I have this query, which I'm going to use for a Group output.

<cfquery name="allContents" datasource="#request.DSN#">
SELECT contentType.id, 
contentType.typeOfContent, 
contentType.orderOfType, 
contentType.active, 
contentType.showHeading,
content.id, 
content.title, 
content.showBetweenDates, 
content.showFrom, 
content.showTo, 
content.orderOfContent, 
content.active, 
content.showOnMenu
FROM contentType LEFT OUTER JOIN content ON contentType.id = content.contentTypeId
ORDER BY contentType.orderOfType, content.orderOfContent		</cfquery>

When I use CFOUTPUT to get #content.id#, ColdFusion gives me this error:

coldfusion.runtime.UndefinedElementException: Element TYPEOFCONTENT is undefined in CONTENTTYPE

... so I figure that the . notation is causing it a problem.

So then I change the states in the SQL to

SELECT contentType.id as contentTypeId

And the MSAccess driver tells me that the statement is not supported!

Now I could get round the problem by just making sure that all the field names are unique across the tables, but I was curious if there was something else I could do to avoid it?

 

[r937]

yes, the dot notation is a problem, coldfusion does not know the column names as prefixed by their table names, coldfusion only knows the column names in the result set, period

your column alias approach in the sql is correct

please show again the exact query that got the error



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[Rayz66]

Well, I typed in the code again to get hold an example for you, and it worked!

I must've had some kind of syntax error in my first attempt; the error message threw me though.

Thanks for your help.