Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Jul 2008
    Posts
    31
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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!

    Code:
     <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>

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    When you put '...' around something in SQL, you turn it into a *LITERAL STRING*.

    So your ON condition is saying
    Code:
    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:
    Code:
    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 ~ ).

  • #3
    New Coder
    Join Date
    Jul 2008
    Posts
    31
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.

  • #4
    Regular Coder
    Join Date
    Feb 2009
    Location
    NJ, USA
    Posts
    476
    Thanks
    2
    Thanked 70 Times in 69 Posts
    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?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •