Solved

help with query of queries (in memory query)

Posted on 2004-08-14
4
233 Views
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#
</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
Comment
Question by:joeinfo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
reitzen earned 125 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#
</cfquery>

HTH
Rob
0
 
LVL 14

Assisted Solution

by:Renante Entera
Renante Entera earned 125 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)#)
</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
 

Author Comment

by:joeinfo
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!
0
 
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 :-)
0

Featured Post

Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

687 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