joeinfo
asked on
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.cityNa
<td>#cityState.stateName#<
</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!
<!--- 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.cityNa
<td>#cityState.stateName#<
</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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh! Thanks for the compliments joeinfo.
I'm glad that I have helped you.
Hope to help you again soon...
Best wishes!
eNTRANCE2002 :-)
I'm glad that I have helped you.
Hope to help you again soon...
Best wishes!
eNTRANCE2002 :-)
ASKER