Skip to main content
Y
yz_rules
Participating Frequently
April 30, 2009
Answered

SQL Trouble

  • April 30, 2009
  • 1 reply
  • 604 views

I'm getting a bit stuck with using this SQL, basicly I want to do two inner joins. Okay, so thats probably not what I want to, theres probably a better way of doing what I'm trying to do, I just don't know what it is and can't figure it out. Here is my code:

<cfquery name="Recordset1" datasource="a8022782-access">
SELECT * FROM Reviews
INNER JOIN Videos ON Reviews.video_id = Videos.id
INNER JOIN Members ON Reviews.member_id = Members.member_id
</cfquery>

Hope thats enough info.

Thanks in advance,

Andrew

    This topic has been closed for replies.
    Correct answer -__cfSearching__-

    Haha, well I just get this error:

    Syntax error (missing operator) in query expression 'Reviews.video_id = Videos.id INNER JOIN Members ON Reviews.member_id = Members.member_id'


    There is nothing wrong with it that I can see.  Unless you are using MS Access.  IIRC, it requires some funky ( ..) around the joins.  Something along these lines.  Though you would get a more accurate answer by using Access' query builder

    SELECT    ColumnNames...
    FROM    ((Reviews
                     INNER JOIN Members ON Reviews.member_id = Members.member_id)

                     INNER JOIN Videos ON Reviews.video_id = Videos.id)

    1 reply

    -
    -__cfSearching__-
    Inspiring
    April 30, 2009

    So what is it you are actually trying to do ... or what is the problem with the query you posted?

    At a glance it looks okay, except that you should not use SELECT *   with multi-table queries. That can cause problems when the same column name exists in more than one table. CF may not know which one to use.

    Y
    yz_rulesAuthor
    Participating Frequently
    April 30, 2009

    Okay, I'll change the * bit. Well basicly I have a user reviews table for videos, called reviews and I want the title to appear from the videos table and the users first and last name to appear from the user table.

    <table width="100%">
    <tr>
    <th>Title</th>
    <th>Detail</th>
    <th>Rating</th>
    <th>Autor</th>
    </tr>
    <cfoutput query="Recordset1">
    <tr>
    <td><a href="dvd-one.cfm?id=#id#">#title#</td>
    <td>#content#</td>
    <td>#rating#</td>
    <td>#first_name# #last_name#</td>
    </tr>
    </cfoutput>
    </table>

    Cheers!

    -
    -__cfSearching__-
    Inspiring
    April 30, 2009

    Okay... but I am still not hearing what the problem is .. ;-)

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices