something like this maybe?
<cfquery name="qData" datasource="ipIntranet">
SELECT s.statename, c.cityname, 'capital' as type
FROM cities c
INNER JOIN states s on c.stateid = s.stateID
WHERE capital = 1
UNION
SELECT s.statename, c.cityname, 'largest' as type
FROM cities c
INNER JOIN states s on c.stateid = s.stateID
WHERE largest = 1
</cfquery>
>>> [EMAIL PROTECTED] 7/07/2005 1:34 pm >>>
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/
