You could do a subquery in your select statement SELECT (SELECT TOP 1 CapitalName FROM Cities c WHERE s.ID = c.State_ID AND c.Capital = 1) AS Capital FROM State s
And then just add another one for largest -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Chad Renando Sent: Thursday, 7 July 2005 3:35 PM To: CFAussie Mailing List Subject: [cfaussie] Do I loop or do I query... 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/ ---------------------------------------------------- This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. ---------------------------------------------------- --- 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/
