-Cameron
-----------------
Cameron Childress
Sumo Consulting Inc
---
land: 858.509.3098
cell: 678.637.5072
aim: cameroncf
email: [EMAIL PROTECTED]
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 08, 2004 1:46 PM
To: CF-Talk
Subject: Re: Database Joins
I think you should keep them separate, and cache the country query, since
it's likely never to change...(using cfquery's cachedwithin parameter or
even writing that to static html and using that)... That's more
performant than worrying about condensing 2 queries into one.... If you
want one query though for other reasons, you can just do a select on all
the PropertyCountryList and LEFT JOIN it to PropertyAgentList ON the
country attribute. Then there'd be only one non-empty PropertyAgentID...
But I highly recommend considering the 2 queries very differently as one
is site-wide and one as user-specific.
HTH
d
On Sun, 8 Feb 2004, Stuart Kidd wrote:
> Hi guys,
>
> I'm trying to do one database query (SELECT) without having to do 2
> separate ones.
>
> I have two tables. One called PropertyAgent and then another called
> PropertyCountryList.
>
> In PropertyAgent, there is a field called PropertyAgentCountry (numeric)
> which was inserted originally using a choice of the field
> PropertyCountryListID (numeric) from table PropertyAgentCountry.
>
>
> At the moment I have two queries, but I'm thinking there must be away
> around it.
>
> // getting the agent details info (which country he is from)
> <CFQUERY NAME="AgentDetails" datasource="020">
> SELECT PropertyAgentID, PropertyAgentCountry
> FROM PropertyAgent
> WHERE PropertyAgentID = #Client.PropertyAgentID#
> </CFQUERY>
>
> // getting list of countries to populate form
> <CFQUERY NAME="GetPropertyCountryList" datasource="020">
> SELECT PropertyCountryListID, PropertyCountryListName
> FROM PropertyCountryList
> ORDER by PropertyCountryListName ASC
> </CFQUERY>
>
> // outputting to the form.
>
> <cfselect name="PropertyAgentCountry" class="FormSTYLE2">
> <cfoutput query="GetPropertyCountryList">
> <option value="#PropertyCountryListID#" <cfif PropertyCountryListID is
> AgentDetails.PropertyAgentCountry>selected</cfif>>#PropertyCountryListNa
> me#</option>
> </cfoutput>
> </cfselect>
>
> Any ideas greatly appreciated,
>
> Stuart
>
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
