CF QoQ is throwing runtime error. "Column reference is not a column in any of the tables of the FROM table list."

Question

In my code, I first create the Query Object:

<cfset memberData = QueryNew('slug,pos,firstname,lastname,email') />
<cfset temp = QueryAddRow(memberData, #numMembers#) />
<!--- LOOP POPULATES QUERY OBJECT --->
<cfloop...</cfloop>

I can then verify that it has been populated by running the following (which outputs as expected):

<cfoutput query="memberData">
#slug# - #pos#<br>
</cfoutput>

I then try to query the memberData Query Object and all hell breaks loose. If I run:

<cfquery name="members" dbtype="query">
    SELECT slug,pos,firstname,lastname
    FROM memberData
    WHERE slug = #slug#
</cfquery>

I get this error:

Query Of Queries runtime error.
The select column reference [university] is not a column in any of the tables of the FROM table list.

In the output test mentioned above, I can verify that "university" is one of the values in the slug column. Clearly I'm missing something in my approach, but I'm baffled as to what it might be. Any help would be greatly appreciated!

Just want to point out that in `` the temp variable is unnecessary. `` will work just as well. — ale, Sep 12 '12 at 13:12

score 3 · Accepted Answer · edited Sep 19 '12 at 00:42

Query Of Queries runtime error.
The select column reference [university] is not a column in any of the tables of the FROM table list

Your error was caused by absence of quotes in where clause and nothing else:

This expression find rows where the value in slug column equals the value in CF slug variable (String):

      WHERE slug = '#slug#'

On the other hand this expression means find rows where value in the slug column equals the value contained in the column named in the CF slug variable (String):

      WHERE slug = #slug#

The most likely cause of why you needed to change to SELECT * is CF query caching. So changing it back now should solve the problem. And always use <cfqueryparam .../> as suggested by "Al Everett"

ie By omitting the quotes you are telling the database "univerisity" is a *column* in your table, not a simple string. — Leigh, Sep 19 '12 at 00:58

score 2 · Answer 2 · answered Sep 12 '12 at 07:49

Well, it's not quite answering the question asked, but it's close enough for what I needed:

<cfquery name="members" dbtype="query">
    SELECT *
    FROM memberData
    WHERE slug = '#slug#'
</cfquery>

I had tried the wrapping #slug# in single quotes prior to posting with no success, but doing that plus changing the query to SELECT * fixed the issue. For my content, * only adds one more value retrieved, so not really a problem in slowing down the process.

Using `` (yes, even in a QoQ) would have prevented this issue. — ale, Sep 12 '12 at 13:14

CF QoQ is throwing runtime error. "Column reference is not a column in any of the tables of the FROM table list."

2 Answers2