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
