• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

help with query of queries (in memory query)

can anyone please tell me why this is not working: i'll paste my code first and the browser error message at the end:

<!--- first i query citiesTable for details on selected city whose cityID was passed in URL  --->
<!--- for example, the URL may look like /details?cityID=400 --->
<!--- citiesTable has 3 columns - cityID (data type: AutoNumber) cityName (Text) and stateID (Number) --->

<cfquery name="getCityDetails" datasource="test">
      SELECT cityName, stateID
      FROM citiesTable
      WHERE cityID=#url.cityID#
</cfquery>

<!--- statesTable has 2 columns - stateID (AutoNumber) and stateName (Text) --->

<cfquery name="getAllStates" datasource="test">
      SELECT stateID, stateName
      FROM statesTable
</cfquery>

<!--- here i try to get the state name by matching the stateID from both queries  --->

<cfquery name="cityState" dbtype="query">
      SELECT stateName
      FROM getAllStates
      WHERE getCityDetails.stateID = getAllStates.stateID
</cfquery>

<html>
<head></head>
      <body>
                  <cfoutput>
                        <td>#getCityDetails.cityName#</td>
                        <td>#cityState.stateName#</td>
                        </cfoutput>
      </body>

</html>

the browser error i am getting is:

Query Of Queries runtime error.
The selected column reference "getCityDetails.stateID" does not match any table in the FROM table list.

(there definitely is a stateID column in both the citiesTable and the stateTable.)

thank you for you suggestions!
0
joeinfo
Asked:
joeinfo
  • 2
2 Solutions
 
reitzenCommented:
CF is looking at "getCityDetails.stateID" as a column when it should be looking at it as a variable.

Wrap pound signs around it and you should be on your way:
<cfquery name="cityState" dbType="query">
    select stateName
    from getAllStates
    where stateID = #getCityDetails.stateID#
</cfquery>

HTH
Rob
0
 
Renante EnteraCommented:
Yah! I agree with reitzen on the part of the column which CF is looking at.

But on the part of the query, I think the result will not totally give you all city details because of the condition "where stateID = #getCityDetails.stateID#".  It will just return one record with the value of the stateID on the first query.  Do you agree with this reitzen?

So, the query should be :

<cfquery name="cityState" dbType="query">
    select stateName
    from getAllStates
    where stateID in (#ValueList(getCityDetails.stateID)#)
</cfquery>

Try running this query and compare it without using CF function ValueList().

Then by the way, I have a suggestion.  You can optimize your query using JOIN statement.

You can have it in this way :

<cfquery name="getCityDetails" datasource="test">
  SELECT c.cityID, c.cityName, c.stateID, g.stateName
  FROM citiesTable c
  INNER JOIN getAllStates g
    ON g.stateID = c.stateID
  WHERE c.cityID=#url.cityID#
</cfquery>

For some tutorials about using JOIN statement, you can visit this site : http://www.w3schools.com/sql/sql_join.asp


Regards!
eNTRANCE2002 :-)
0
 
joeinfoAuthor Commented:
reitzen's answer worked perfectly but entrance2002's suggestion to visit w3schools.com and try using a "join" was a gem. visiting that page before i began would have made my work a lot easier. thank you to both!
0
 
Renante EnteraCommented:
Oh! Thanks for the compliments joeinfo.

I'm glad that I have helped you.

Hope to help you again soon...


Best wishes!
eNTRANCE2002 :-)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now