help with query of queries (in memory query)

Posted on 2004-08-14
Medium Priority
Last Modified: 2013-12-24
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#

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

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

<!--- 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



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!
Question by:joeinfo
  • 2

Accepted Solution

reitzen earned 500 total points
ID: 11798970
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#

LVL 14

Assisted Solution

by:Renante Entera
Renante Entera earned 500 total points
ID: 11807257
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)#)

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#

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

eNTRANCE2002 :-)

Author Comment

ID: 12040814
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!
LVL 14

Expert Comment

by:Renante Entera
ID: 12040845
Oh! Thanks for the compliments joeinfo.

I'm glad that I have helped you.

Hope to help you again soon...

Best wishes!
eNTRANCE2002 :-)

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Media Temple is proud to announce our partnership with the Society of Digital Agencies (SoDA) as their exclusive hosting partner.
The following information will get you familiar with your new DV server, including the (mt) Account Center, the Plesk Control Panel, our world-renowned support department and the rest of the (mt) tools that come with your new service.
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question