You'll only need one query. Try this. Off the cuff, syntax may be wrong
but you get the idea.
<CFQUERY NAME="WhichCity"...>
SELECT CITIES.CityId, CITIES.Capital, CITIES.Largest, CITIES.CityName, etc.
FROM CITIES, STATES
WHERE CITIES.StateId = STATES.StateId
AND STATES.StateId = '#SelectedState#'
AND (CITIES.Capital = 1 OR CITIES.Largest = 1)
ORDER BY CITIES.Capital
</CFQUERY>
Should return 1 or 2 rows, 1 if largest city is also the state capital.
If 2 cities are returned, the largest city is #WhichCity.CityName[1]#
and the capital city is #WhichCity.CityName[2]#
--
Regards: Ayudh
+----------------------------------------------------------------+
| SOAP is the glue! Hook up your server directly to your bank. |
| Connect to VeriPay xServ, the Australian Payments Web Service. |
| Reliable, Secure, FAST: http://www.xilo.com/xserv |
+----------------------------------------------------------------+
Chad Renando wrote:
Say I have a table of States and another table of Cities. In my
Cities table, I have a bit field for Capital (Yes if the city is the
capital) and a bit field for largest (Yes if it is the largest in the
State).
Now say I want to return the capital and largest city for a given
state. What is the most efficient way to do this?
Here's what I have come up with:
1. Run 2 queries, one with "WHERE Capital = 1", the other with "WHERE
Largest = 1".
2. Run 1 query and loop through it, setting the cities if Capital EQ 1
and if Largets EQ 1.
I know there has to be a better way to do this. and I'm also hoping
one of you will let go of the IP on how to do it. ;)
Chad
who lets his IP go on a regular basis
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
---
You are currently subscribed to cfaussie as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
