Bryan,
This works for returning two separate result sets (one for the selected town and
another for the clustered towns if
available) The problem is that the client wants to be able to page through all the
results of the clustered towns
query starting with the actual town selected no matter where it falls alphabetically
in the cluster. Then continue
though the rest of the towns in alphabetical order.
Unless I am missing something there is no way for me to actually page through a
complete resultset here because if
I am using the the first query the pagination will not continue after the last entry
for the seleted town and if
I page through the second resultset I will not be including the selected town
information.
Thanks,
Frederic
Bryan Love wrote:
> run two queries:
>
> The first one gets all info for the selected town and the second gets all
> info for towns of the same cluster (if it's not blank) but NOT for the
> selected town like so (set the select list to a variable so you don't have
> to mess with two queries later on):
>
> <cfset selList = "T.RMOTown as town, T.Chamberwebaddress as webad,
> T.Cllustername, A.PrimaryName as pname, A.Description as descipt
> ,a.PhysicalAddress +', '+a.City +', '+a.State +', '+a.ZipCode as address,
> a.Phone as phone, a.TollFreePhone as tfphone,EMailAddress as email,
> a.WebSite as web, cllustername">
>
> <cfquery name="get_cluster" datasource="#DS_1#" dbtype="ODBC">
> SELECT #preserveSingleQuotes(selList)#
> FROM RMOTownName T
> WHERE T.RMOTown='#RMOTOWN#'
> </cfquery>
>
> <cfif get_cluster.Cllustername IS NOT "" >
> <cfquery name="get_1" datasource="#DS_1#" dbtype="ODBC">
> SELECT #preserveSingleQuotes(selList)#
> FROM RMOTownName T, Attractions A
> WHERE A.RMOTown=T.RMOTown AND
> T.RMOTown<>'#RMOTOWN#' AND
> Cllustername IN (SELECT Cllustername FROM RMOTownName WHERE
> RMOTown='#RMOTOWN#')
> ORDER BY T.rmotown, A.PrimaryName
> </cfquery>
> </cfif>
>
> Bryan Love ACP
> Internet Application Developer
> [EMAIL PROTECTED]
>
>
> -----Original Message-----
> From: Freddy [mailto:[EMAIL PROTECTED]]
> Sent: Monday, March 19, 2001 3:50 PM
> To: CF-Talk
> Subject: QUERY TROUBLE
>
> I have a query that returns a list of attractions in a certain town. If the
> town is in a "cluster" (a group of
> related towns) it also needs to order by the selected town first and then
> list the rest of the towns
> alphabetically.
> How would I get this output from the following queries?
>
> <cfquery name="get_cluster" datasource="#DS_1#" dbtype="ODBC">
> SELECT T.RMOTown, T.Cllustername
> FROM RMOTownName T
> WHERE T.RMOTown='#RMOTOWN#'
> </cfquery>
>
> <cfquery name="get_1" datasource="#DS_1#" dbtype="ODBC">
> SELECT T.RMOTown as town, T.Chamberwebaddress as webad,
> T.Cllustername, A.PrimaryName as pname, A.Description as descipt
> ,a.PhysicalAddress +', '+a.City +', '+a.State +', '+a.ZipCode as
> address, a.Phone as phone, a.TollFreePhone as tfphone,EMailAddress
> as
> email, a.WebSite as web
> FROM RMOTownName T, Attractions A
> <cfif LEN(trim(get_cluster.Cllustername)) gt 0>
> WHERE Cllustername IN (SELECT Cllustername FROM RMOTownName
> WHERE
> RMOTown='#RMOTOWN#') AND A.RMOTown=T.RMOTown
> <cfelse>
> WHERE T.RMOTown='#RMOTOWN# ' AND A.RMOTown=T.RMOTown
> </cfif>
> order by T.rmotown, A.PrimaryName
> </cfquery>
> Is there something I can do in the ORDER BY clause that will allow for
> this?
> I know I can use an if statement in the output to output the selected
> town's info first but this will probibaly mess with the way my pagination
> through the results works and is not the
> most efficient
> method.
> Thanks,
> Frederic.
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
