Solved

help with query of queries (in memory query)

Posted on 2004-08-14
4
224 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
  • 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

912 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now