Join Expression Not Supported

I have two tables, USERS and ART. Both contain a USERID field, which is a number linked to both.

When I output from the art table, I want to display the userNAME of the person, rather than their userID.


I've established I need to use JOINS. But after spending EIGHT HOURS on this today, nothing is working and I don't know why!! Also, do I link the IDs or do I link the art.USERID with the users.USERNAME? I keep getting conflicting info but I would have thought with the IDs being linked already it'd be the username?? :/

Seriously, what's wrong with this!


<cfquery name="catdetails" datasource="062105cs06sr">
SELECT users.username, users.userid, art.arttitle, art.arturl, art.artcat, art.artdatecreated, art.artdescription, art.keyword
FROM ( art
INNER JOIN users
ON 'art.userid' = 'users.userid' )
</cfquery>

When you put '...' around something in SQL, you turn it into a *LITERAL STRING*.

So your ON condition is saying

ON the occasion that the *STRING* art.userid is the same as the *STRING* users.userid

And of course those STRINGS will *NEVER* be equal.

Now get rid of the apostrophes.

Also, get rid of the parentheses.

Finally, it is possible that users and/or userid may be keywords in whatever database you are using. What DB are you using??? SQL Server? Access? MySQL????

Whether those are keywords *AND* how to handle the case where they are will depend on what DB you are using. *IF* needed, possible fixes include:

Access:
SELECT users.username, users.userid, art.arttitle, art.arturl, art.artcat, art.artdatecreated, art.artdescription, art.keyword
FROM art INNER JOIN users ON art.[userid] = users.[userid]

MySQL:
SELECT users.username, users.userid, art.arttitle, art.arturl, art.artcat, art.artdatecreated, art.artdescription, art.keyword
FROM art INNER JOIN users ON art.`userid` = `users`.`userid`

SQL Server:
SELECT users.username, users.userid, art.arttitle, art.arturl, art.artcat, art.artdatecreated, art.artdescription, art.keyword
FROM art INNER JOIN users ON art."userid" = users."userid"


Note: I don't think userid is a poison keyword in any of those DBs, but if it happens to be, that code will fix it.
Note: Those are BACK TICKS in the MySQL code (the character on the same keyboard key as ~ ).

I tried this but I am getting this now:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Type mismatch in expression.

The error occurred in F:\wwwroot\ug06\cs06sr\cf\category.cfm: line 16

14 :
15 : <h1>Categories</h1><br>
16 : <cfquery name="catdetails" datasource="062105cs06sr">
17 : SELECT users.username, users.userid, art.arttitle, art.arturl, art.artcat, art.artdatecreated, art.artdescription, art.keyword
18 : FROM art INNER JOIN users ON art.[userid] = users.[userid]

Do I actually want to link the USERID and Art.USERID because they're already linked in the database? :/

I'm using Microsoft Access. Thanks in advance for any light you can shed on the situation.

Are both the art.[userID] column and the users.[userID] column number fields? If one is a number field, and one is a text field, you'll get a "type mismatch" error. In other words, the database cannot compare numbers with text characters.

Don't know much about access, but what do you mean that they are "linked" in the database? Also, I vaguely remember something about setting up "expressions" for tables in access (way back when). Have you set any of those up?

Just to let you know by the way, this isn't much of a ColdFusion issue per se, but more of a database issue. You're just querying a database within ColdFusion. That being said, if you run that exact same query within access itself, do you get the same error?