[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

help with query of queries (in memory query)

Posted on 2004-08-14
4
Medium Priority
?
241 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 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#
</cfquery>

HTH
Rob
0
 
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)#)
</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

Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

649 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