RE: Query Trouble
Love this list! Thanks. That's the ticket. >>> [EMAIL PROTECTED] 12/04/02 04:01PM >>> You need to do a left outer join - SELECT vendors.vendorid, vendors.vendor_name,products.product_name FROM vendors LEFT OUTER JOIN products ON vendors.id = products.vendorid ORDER BY vendorid Give that a shot. Adam. > -Original Message- > From: Willy Ray [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, December 04, 2002 4:50 PM > To: CF-Talk > Subject: SOT: Query Trouble > > > Ok, say I have a table of vendors and a table of products, > and I want to > list out all the vendors and their products: > > > SELECT vendors.vendor_name,products.product_name > FROM vendors, products > WHERE vendors.id = products.vendorid > ORDER BY vendorid > > > > Then: > > > > #qGetData.vendor_name#: > > #qGetData.product_name#, > > > > > > This gives me a good list of all my vendors, with a somewhat sloppy > comma delimited list of their products after. But what if I have > vendors who don't currently have products in the product table, and I > want to list them anyway? It's like a, where-are-the-vendors and BTW, > here-are-their-products kind of deal. > > Another way to put it. I have three vendors and the second one has no > products, I need my list to look like this: > > Vendor A: mouse traps, cat food, > Vendor B: > Vendor C: applesauce, condensed milk, > > But the above query doesn't do that. Any help? > > Willy > > > > - > Willy Ray > Web Applications Developer > Certified Advanced ColdFusion Developer > Westminster College > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: Query Trouble
You need to do a left outer join - SELECT vendors.vendorid, vendors.vendor_name,products.product_name FROM vendors LEFT OUTER JOIN products ON vendors.id = products.vendorid ORDER BY vendorid Give that a shot. Adam. > -Original Message- > From: Willy Ray [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, December 04, 2002 4:50 PM > To: CF-Talk > Subject: SOT: Query Trouble > > > Ok, say I have a table of vendors and a table of products, > and I want to > list out all the vendors and their products: > > > SELECT vendors.vendor_name,products.product_name > FROM vendors, products > WHERE vendors.id = products.vendorid > ORDER BY vendorid > > > > Then: > > > > #qGetData.vendor_name#: > > #qGetData.product_name#, > > > > > > This gives me a good list of all my vendors, with a somewhat sloppy > comma delimited list of their products after. But what if I have > vendors who don't currently have products in the product table, and I > want to list them anyway? It's like a, where-are-the-vendors and BTW, > here-are-their-products kind of deal. > > Another way to put it. I have three vendors and the second one has no > products, I need my list to look like this: > > Vendor A: mouse traps, cat food, > Vendor B: > Vendor C: applesauce, condensed milk, > > But the above query doesn't do that. Any help? > > Willy > > > > - > Willy Ray > Web Applications Developer > Certified Advanced ColdFusion Developer > Westminster College > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: Query Trouble
I've used many nested queries too. How about this code? This way, you can loop through and query each record of the qGroups query. Also, I dont think you need two qualifiers in the cfif statement. The one making sure there's a record is sufficient. Anyhow I hope this works! SELECT DISTINCT EventType FROM Events SELECT EventID,EventType,X,Y,EventText FROM Events WHERE EventType = '#EventType#' #qGroups.CurrentRow#,#qSpecificLocations.CurrentRow#,#qSpecificLocations.X#, #qSpecificLocations.Y#); ~Val - Original Message - From: "Yager, Brian T Contractor/NCCIM" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, April 16, 2002 2:54 PM Subject: RE: Query Trouble > That's not true. I have MANY apps that have nested queries. > > Brian Yager > President - North AL Cold Fusion Users Group > Sr. Systems Analyst > NCCIM/CIC > [EMAIL PROTECTED] > (256) 842-8342 > > > -Original Message- > From: Jerry Johnson [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, April 16, 2002 1:41 PM > To: CF-Talk > Subject: Re: Query Trouble > > > This has got to be in the FAQ. By the way, where's the FAQ? > > To cut from a similar answer a few days ago: > >My understanding is that you can only run through one query at a time (with > CF handling the currentRow your on.) > >So you are going to want to only rely on the query= for one of your two > loops. Or maybe none. > >This is not the only way, but it should work. > >Other people will chime in with the correct (and faster) ways, but this > should get you going. > > > >#SecondQuery['RowName'][cRow]# > >#SecondQuery['RowName'][cRow]# >#first['RowName'][cRow2]# > > > > > If this doesn't help, shout out and I'll try a more specific answer. > Jerry Johnson > > > > >>> [EMAIL PROTECTED] 04/16/02 02:29PM >>> > Can someone point out why this query is only getting the last group of > records? > A second set of eyes would really help! > > If the query qGroups returns the following eventtypes: ItemA, ItemB, ItemC > Only group ItemC and records that are in ItemC will print out. > > -- > > SELECT DISTINCT EventType > FROM Events > > > > > SELECT EventID,EventType,X,Y,EventText > FROM Events > WHERE EventType = '#qGroups.EventType#' > > > qSpecificLocations.RecordCount GTE 1> > > > #qGroups.CurrentRow#,#qSpecificLocations.CurrentRow#,#qSpecificLocations.X#, > #qSpecificLocations.Y#); > > > > > -- > Thanks, > Dave > > > > > > > __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Query Trouble
Sure, I was overly simplistic. But I didn't want to try to explain nested loops and the group attribute. I was really trying to show the mechanism for accessing the query rows and columns directly, without needing to rely on the query attribute of CFOUTPUT and CFLOOP. But if you are going to suggest how to handle this guy's query needs using two nested query loops, you should give him an example of how it would work. Jerry Johnson >>> [EMAIL PROTECTED] 04/16/02 02:54PM >>> That's not true. I have MANY apps that have nested queries. Brian Yager President - North AL Cold Fusion Users Group Sr. Systems Analyst NCCIM/CIC [EMAIL PROTECTED] (256) 842-8342 -Original Message- From: Jerry Johnson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 16, 2002 1:41 PM To: CF-Talk Subject: Re: Query Trouble This has got to be in the FAQ. By the way, where's the FAQ? To cut from a similar answer a few days ago: My understanding is that you can only run through one query at a time (with CF handling the currentRow your on.) So you are going to want to only rely on the query= for one of your two loops. Or maybe none. This is not the only way, but it should work. Other people will chime in with the correct (and faster) ways, but this should get you going. #SecondQuery['RowName'][cRow]# #SecondQuery['RowName'][cRow]# #first['RowName'][cRow2]# If this doesn't help, shout out and I'll try a more specific answer. Jerry Johnson >>> [EMAIL PROTECTED] 04/16/02 02:29PM >>> Can someone point out why this query is only getting the last group of records? A second set of eyes would really help! If the query qGroups returns the following eventtypes: ItemA, ItemB, ItemC Only group ItemC and records that are in ItemC will print out. -- SELECT DISTINCT EventType FROM Events SELECT EventID,EventType,X,Y,EventText FROM Events WHERE EventType = '#qGroups.EventType#' #qGroups.CurrentRow#,#qSpecificLocations.CurrentRow#,#qSpecificLocations.X#, #qSpecificLocations.Y#); -- Thanks, Dave __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Query Trouble
That's not true. I have MANY apps that have nested queries. Brian Yager President - North AL Cold Fusion Users Group Sr. Systems Analyst NCCIM/CIC [EMAIL PROTECTED] (256) 842-8342 -Original Message- From: Jerry Johnson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 16, 2002 1:41 PM To: CF-Talk Subject: Re: Query Trouble This has got to be in the FAQ. By the way, where's the FAQ? To cut from a similar answer a few days ago: My understanding is that you can only run through one query at a time (with CF handling the currentRow your on.) So you are going to want to only rely on the query= for one of your two loops. Or maybe none. This is not the only way, but it should work. Other people will chime in with the correct (and faster) ways, but this should get you going. #SecondQuery['RowName'][cRow]# #SecondQuery['RowName'][cRow]# #first['RowName'][cRow2]# If this doesn't help, shout out and I'll try a more specific answer. Jerry Johnson >>> [EMAIL PROTECTED] 04/16/02 02:29PM >>> Can someone point out why this query is only getting the last group of records? A second set of eyes would really help! If the query qGroups returns the following eventtypes: ItemA, ItemB, ItemC Only group ItemC and records that are in ItemC will print out. -- SELECT DISTINCT EventType FROM Events SELECT EventID,EventType,X,Y,EventText FROM Events WHERE EventType = '#qGroups.EventType#' #qGroups.CurrentRow#,#qSpecificLocations.CurrentRow#,#qSpecificLocations.X#, #qSpecificLocations.Y#); -- Thanks, Dave __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Query Trouble
Why the 2 queries? How about this? SELECT DISTINCT EventID,EventType,X,Y,EventText FROM Events #qGroups.CurrentRow#, #qSpecificLocations.CurrentRow#, #qSpecificLocations.X#, #qSpecificLocations.Y# It's not exactly clear what you're trying to do but if this isn't quite right you can probably get what you want by adding a group attribute to the cfoutput. -Original Message- From: Bosky, Dave [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 16, 2002 2:29 PM To: CF-Talk Subject: Query Trouble Can someone point out why this query is only getting the last group of records? A second set of eyes would really help! If the query qGroups returns the following eventtypes: ItemA, ItemB, ItemC Only group ItemC and records that are in ItemC will print out. -- SELECT DISTINCT EventType FROM Events SELECT EventID,EventType,X,Y,EventText FROM Events WHERE EventType = '#qGroups.EventType#' #qGroups.CurrentRow#,#qSpecificLocations.CurrentRow#,#qSpecificLocations.X#, #qSpecificLocations.Y#); -- Thanks, Dave __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Query Trouble
This has got to be in the FAQ. By the way, where's the FAQ? To cut from a similar answer a few days ago: My understanding is that you can only run through one query at a time (with CF handling the currentRow your on.) So you are going to want to only rely on the query= for one of your two loops. Or maybe none. This is not the only way, but it should work. Other people will chime in with the correct (and faster) ways, but this should get you going. #SecondQuery['RowName'][cRow]# #SecondQuery['RowName'][cRow]# #first['RowName'][cRow2]# If this doesn't help, shout out and I'll try a more specific answer. Jerry Johnson >>> [EMAIL PROTECTED] 04/16/02 02:29PM >>> Can someone point out why this query is only getting the last group of records? A second set of eyes would really help! If the query qGroups returns the following eventtypes: ItemA, ItemB, ItemC Only group ItemC and records that are in ItemC will print out. -- SELECT DISTINCT EventType FROM Events SELECT EventID,EventType,X,Y,EventText FROM Events WHERE EventType = '#qGroups.EventType#' #qGroups.CurrentRow#,#qSpecificLocations.CurrentRow#,#qSpecificLocations.X#, #qSpecificLocations.Y#); -- Thanks, Dave __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: query trouble
Here are my standard steps for dealing with tough query problems: 1) View the CF-generated SQL statement by turning on debugging (or by just -ing the code instead of -ing it. 2) Look at the generated statement for obvious errors. 3) Try to run the generated SQL directly in your db, using some db tool (e.g., Enterprise Manager) 4) Debug in the db, rather than in CF. -Original Message- From: Jon Tillman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 28, 2001 12:43 PM To: CF-Talk Subject: query trouble -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I cannot for the life of me figure out what is wrong with this query. I am getting a Syntax Error or Access Violation Error: INSERT INTO tblClient (ClientTemplateID, ClientDomain, ClientDomainAction, ClientPackage, ClientTerms, ClientAddress_1, ClientAddress_2, ClientCity, ClientState, ClientPostalCode, ClientCountry, ClientPhone, ClientPhone_2, ClientEmail, ClientFName, ClientMI, ClientLName, ClientCCNumber, ClientCCExpire, ClientCCType, ClientComments, ClientLogin, ClientPassword, ClientNew, ClientPrice) VALUES (#session.TemplateID#, '#session.Domain#', '#session.DomainAction#', #session.package#, '#session.terms#', '#session.Address_1#', '#session.Address_2#', '#session.City#', '#session.State#', #session.PostalCode#, '#session.Country#', #session.Phone#, #session.Phone2#, '#session.Email#', '#session.FName#', '#session.MI#', '#session.LName#', #Right(session.CCNumber, 4)#, #CreateODBCDateTime(CreateDate(session.CCexpYr, session.CCexpMo, 1))#, '#session.CCType#', '#session.Conmments#', '#session.Email#', '#password#', 'yes', #application.packageprice#) Any help would be much appreciated. - -- Jon Tillman http://www.eruditum.org Next thing they'll want is a word-processor that auto-updates quake mods, gets stock quotes, waters the office plants, walks their dog, orders a pizza, and is connected to a client/employee database. -BEGIN PGP SIGNATURE- Version: PGP 6.5.1i iQA/AwUBOsIwxtga7tZtnIOtEQK4ZQCeLwq7ZA3B8XpP+dvqLXeieDZO9eYAniLZ LWIucx8XRxjBQUe3/rvimj22 =EKA8 -END PGP SIGNATURE- ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: query trouble
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 28 March 2001 02:05 pm, you wrote: > What type of field is the "ClientNew" field? Some databases store "yes" > values as 1's or 0's - maybe you are mixing data types. > > ~Becky tis a yes/no field using Access2000 if that helps (sigh) - -- Jon Tillman http://www.eruditum.org Kill one man and you are a murderer. Kill millions and you are a conqueror. Kill everyone and you are God. - --Jean Rostand -BEGIN PGP SIGNATURE- Version: PGP 6.5.1i iQA/AwUBOsI8Kdga7tZtnIOtEQJl6gCfdxv8DbbyxBIJvfQrddTqJ/sjkYQAn3kU f0pekUQ+5I+zLcpC7GF+/kni =5DKC -END PGP SIGNATURE- ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: query trouble
What type of field is the "ClientNew" field? Some databases store "yes" values as 1's or 0's - maybe you are mixing data types. ~Becky Jon Tillman wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > I cannot for the life of me figure out what is wrong with this query. I am > getting a Syntax Error or Access Violation Error: > > > INSERT INTO tblClient > (ClientTemplateID, ClientDomain, ClientDomainAction, > ClientPackage, ClientTerms, ClientAddress_1, > ClientAddress_2, ClientCity, ClientState, ClientPostalCode, > ClientCountry, ClientPhone, ClientPhone_2, ClientEmail, > ClientFName, ClientMI, ClientLName, ClientCCNumber, > ClientCCExpire, ClientCCType, ClientComments, > ClientLogin, ClientPassword, ClientNew, ClientPrice) > VALUES (#session.TemplateID#, '#session.Domain#', > '#session.DomainAction#', #session.package#, >'#session.terms#', '#session.Address_1#', >'#session.Address_2#', '#session.City#', >'#session.State#', #session.PostalCode#, >'#session.Country#', #session.Phone#, >#session.Phone2#, '#session.Email#', >'#session.FName#', '#session.MI#', >'#session.LName#', #Right(session.CCNumber, 4)#, >#CreateODBCDateTime(CreateDate(session.CCexpYr, > session.CCexpMo, 1))#, >'#session.CCType#', '#session.Conmments#', >'#session.Email#', '#password#', 'yes', >#application.packageprice#) > > > Any help would be much appreciated. > > - -- > Jon Tillman > http://www.eruditum.org > > Next thing they'll want is a word-processor that auto-updates quake > mods, gets stock quotes, waters the office plants, walks their dog, > orders a pizza, and is connected to a client/employee database. > > -BEGIN PGP SIGNATURE- > Version: PGP 6.5.1i > > iQA/AwUBOsIwxtga7tZtnIOtEQK4ZQCeLwq7ZA3B8XpP+dvqLXeieDZO9eYAniLZ > LWIucx8XRxjBQUe3/rvimj22 > =EKA8 > -END PGP SIGNATURE- > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: QUERY TROUBLE
Thanks that did the trick. "Howarth, Craig (IBK-NY)" wrote: > Try this: > > I have added an additional column which test for the value of RMOTown and outputs a >0 if it matches otherwise a 1. The same column is added to the order by. This >should work in Access. > > > SELECT T.RMOTown, T.Cllustername > FROM RMOTownName T > WHERE T.RMOTown='#RMOTOWN#' > > > > SELECTiif(T.RMOTown='#RMOTOWN#',0,1) as FirstSort, > 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 > > WHERE Cllustername IN (SELECT Cllustername FROM RMOTownName > WHERE RMOTown='#RMOTOWN#') > AND A.RMOTown=T.RMOTown > > WHERE T.RMOTown='#RMOTOWN#' > AND A.RMOTown=T.RMOTown > > order by iif(T.RMOTown='#RMOTOWN#',0,1), T.rmotown, A.PrimaryName > > > > -Original Message- > > From: Freddy [SMTP:[EMAIL PROTECTED]] > > Sent: Monday, March 19, 2001 6: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? > > > > > > SELECT T.RMOTown, T.Cllustername > > FROM RMOTownName T > > WHERE T.RMOTown='#RMOTOWN#' > > > > > > > > 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 > > > > WHERE Cllustername IN (SELECT Cllustername FROM RMOTownName > > WHERE > > RMOTown='#RMOTOWN#') AND A.RMOTown=T.RMOTown > > > > WHERE T.RMOTown='#RMOTOWN# ' AND A.RMOTown=T.RMOTown > > > > order by T.rmotown, A.PrimaryName > > > > 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: QUERY TROUBLE
Try this: I have added an additional column which test for the value of RMOTown and outputs a 0 if it matches otherwise a 1. The same column is added to the order by. This should work in Access. SELECT T.RMOTown, T.Cllustername FROM RMOTownName T WHERE T.RMOTown='#RMOTOWN#' SELECTiif(T.RMOTown='#RMOTOWN#',0,1) as FirstSort, 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 WHERE Cllustername IN (SELECT Cllustername FROM RMOTownName WHERE RMOTown='#RMOTOWN#') AND A.RMOTown=T.RMOTown WHERE T.RMOTown='#RMOTOWN#' AND A.RMOTown=T.RMOTown order by iif(T.RMOTown='#RMOTOWN#',0,1), T.rmotown, A.PrimaryName > -Original Message- > From: Freddy [SMTP:[EMAIL PROTECTED]] > Sent: Monday, March 19, 2001 6: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? > > > SELECT T.RMOTown, T.Cllustername > FROM RMOTownName T > WHERE T.RMOTown='#RMOTOWN#' > > > > 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 > > WHERE Cllustername IN (SELECT Cllustername FROM RMOTownName > WHERE > RMOTown='#RMOTOWN#') AND A.RMOTown=T.RMOTown > > WHERE T.RMOTown='#RMOTOWN# ' AND A.RMOTown=T.RMOTown > > order by T.rmotown, A.PrimaryName > > 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Query Trouble
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): > > > > > SELECT #preserveSingleQuotes(selList)# > FROM RMOTownName T > WHERE T.RMOTown='#RMOTOWN#' > > > > > 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 > > > > 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? > > > SELECT T.RMOTown, T.Cllustername > FROM RMOTownName T > WHERE T.RMOTown='#RMOTOWN#' > > > > 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 > > WHERE Cllustername IN (SELECT Cllustername FROM RMOTownName > WHERE > RMOTown='#RMOTOWN#') AND A.RMOTown=T.RMOTown > > WHERE T.RMOTown='#RMOTOWN# ' AND A.RMOTown=T.RMOTown > > order by T.rmotown, A.PrimaryName > > 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: QUERY TROUBLE
I am using Access as a database and it does not support the PATINDEX function. Any
other thoughts?
Seva Petrov wrote:
> Frederic,
>
> You can try using PATINDEX to set a bit on the matching town and then order
> on the results of the PATINDEX, which will return the town with the bit set
> first:
>
>
> SELECT PATINDEX('#RMOTOWN#', T.RMOTown) as pmatch,
> 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
>
> WHERE Cllustername IN (SELECT Cllustername FROM RMOTownName
> WHERE
> RMOTown='#RMOTOWN#') AND A.RMOTown=T.RMOTown
>
> WHERE T.RMOTown='#RMOTOWN# ' AND A.RMOTown=T.RMOTown
>
> order by pmatch DESC, by T.rmotown, A.PrimaryName
>
>
> HTH,
> Seva Petrov
>
> > 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?
> >
> >
> > SELECT T.RMOTown, T.Cllustername
> > FROM RMOTownName T
> > WHERE T.RMOTown='#RMOTOWN#'
> >
> >
> >
> > 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
> >
> > WHERE Cllustername IN (SELECT Cllustername FROM RMOTownName
> > WHERE
> > RMOTown='#RMOTOWN#') AND A.RMOTown=T.RMOTown
> >
> > WHERE T.RMOTown='#RMOTOWN# ' AND A.RMOTown=T.RMOTown
> >
> > order by T.rmotown, A.PrimaryName
> >
> > 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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: QUERY TROUBLE
Frederic,
You can try using PATINDEX to set a bit on the matching town and then order
on the results of the PATINDEX, which will return the town with the bit set
first:
SELECT PATINDEX('#RMOTOWN#', T.RMOTown) as pmatch,
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
WHERE Cllustername IN (SELECT Cllustername FROM RMOTownName
WHERE
RMOTown='#RMOTOWN#') AND A.RMOTown=T.RMOTown
WHERE T.RMOTown='#RMOTOWN# ' AND A.RMOTown=T.RMOTown
order by pmatch DESC, by T.rmotown, A.PrimaryName
HTH,
Seva Petrov
> 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?
>
>
> SELECT T.RMOTown, T.Cllustername
> FROM RMOTownName T
> WHERE T.RMOTown='#RMOTOWN#'
>
>
>
> 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
>
> WHERE Cllustername IN (SELECT Cllustername FROM RMOTownName
> WHERE
> RMOTown='#RMOTOWN#') AND A.RMOTown=T.RMOTown
>
> WHERE T.RMOTown='#RMOTOWN# ' AND A.RMOTown=T.RMOTown
>
> order by T.rmotown, A.PrimaryName
>
> 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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: QUERY TROUBLE
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): SELECT #preserveSingleQuotes(selList)# FROM RMOTownName T WHERE T.RMOTown='#RMOTOWN#' 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 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? SELECT T.RMOTown, T.Cllustername FROM RMOTownName T WHERE T.RMOTown='#RMOTOWN#' 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 WHERE Cllustername IN (SELECT Cllustername FROM RMOTownName WHERE RMOTown='#RMOTOWN#') AND A.RMOTown=T.RMOTown WHERE T.RMOTown='#RMOTOWN# ' AND A.RMOTown=T.RMOTown order by T.rmotown, A.PrimaryName 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Query Trouble
You want a LEFT JOIN: Select messages., users., [more fields] FROM messages LEFT JOIN users ON messages.userID = users.userID -Original Message- From: Russell Jones [mailto:[EMAIL PROTECTED]] Sent: Saturday, October 21, 2000 12:10 PM To: CF-Talk Subject: Query Trouble In a forum app, I have a table of messages that I'm going to query for display. A field in that table is called "UserID". Everywhere that "UserID" is not "0", I'd like to query another table for that person's user info. Q: Is this something I could do all in one query, or should I do the first part as one query and then worry about a second query where necessary while I'm outputting the first one .. I'm not sure if this calls for a JOIN, a Left JOIN, a UNION or what ... Any ideas? Thanks -Russ Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
