Re: Dynamic query help.
Wow, I can't believe I didn't think of using a temporary table to store the dynamic data while it's being edited, that's a great idea. I can't use session variables because it's a grid and would be considered a complex data type. (I was under the impression that session variables can only hold simple data types...am I mistaken on that?) In any case, you have been so helpful Justin, I really appreciate it. Thanks a bunch. Thanks Justin, I wasn't aware that a dynamic query was lost once the page was finished loading. Does the same rule apply to structures? Yes, that would apply to all variables unless you store them in persistent memory (e.g. database, session scope, write to a file, etc.). In your case I would use a separate table in the database to store their pending order while it's in progress (you could also store that query object in the session scope if enabled). You can then use AJAX calls from the HTML page when things change to call a ColdFusion template (or CFC method) to update their cart table before finalizing it as an order. ColdFusion can return the updated information via JSON (among other formats) and JavaScript in the HTML can take that and update the HTML display accordingly. Once the order is finalized (i.e. they click submit) then it would post to a ColdFusion page which makes any final adjustments to the cart, finalizes the order, and removes the temporary cart table entries. This can also be good for tracking abandoned checkouts since the data is persistent. -Justin ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348602 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Dynamic query help.
Wow, I can't believe I didn't think of using a temporary table to store the dynamic data while it's being edited, that's a great idea. I can't use session variables because it's a grid and would be considered a complex data type. (I was under the impression that session variables can only hold simple data types...am I mistaken on that?) Session variables can hold any CF data types, not just simple ones. That said, you might still be better served using a temporary table, if you think the process is fairly complicated. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348610 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Dynamic query help.
Thanks Justin, I wasn't aware that a dynamic query was lost once the page was finished loading. Does the same rule apply to structures? Perhaps I can use a structure to hold the data and update it as changes are made. Basically, I don't want the physical database being updated until the submit button is clicked, but the client needs to know the new totals as they make changes, so that their customer can approve the changes before the client submits them to the physical database. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348494 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Dynamic query help.
normal variables only exists for the duration of the CF request. you need to use session scope if you want persistence that is specific to each user. On Mon, Nov 7, 2011 at 6:01 PM, Ray Meade raym...@yahoo.com wrote: Thanks Justin, I wasn't aware that a dynamic query was lost once the page was finished loading. Does the same rule apply to structures? Perhaps I can use a structure to hold the data and update it as changes are made. Basically, I don't want the physical database being updated until the submit button is clicked, but the client needs to know the new totals as they make changes, so that their customer can approve the changes before the client submits them to the physical database. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348495 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Dynamic query help.
Thanks Justin, I wasn't aware that a dynamic query was lost once the page was finished loading. Does the same rule apply to structures? Yes, that would apply to all variables unless you store them in persistent memory (e.g. database, session scope, write to a file, etc.). In your case I would use a separate table in the database to store their pending order while it's in progress (you could also store that query object in the session scope if enabled). You can then use AJAX calls from the HTML page when things change to call a ColdFusion template (or CFC method) to update their cart table before finalizing it as an order. ColdFusion can return the updated information via JSON (among other formats) and JavaScript in the HTML can take that and update the HTML display accordingly. Once the order is finalized (i.e. they click submit) then it would post to a ColdFusion page which makes any final adjustments to the cart, finalizes the order, and removes the temporary cart table entries. This can also be good for tracking abandoned checkouts since the data is persistent. -Justin ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348502 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Dynamic query help.
...I'm not sure how to update the dynamic query. Hi Ray, I don't know much about the mechanics of your application, but remember that once the HTML has been generated and the page loaded on the client, the ColdFusion process is finished and that dynamic query doesn't exist in memory anymore until the next page is loaded (unless you're storing it in a session variable or other persistent memory). For the sake of this case I'll assume that you have a form which is posting changes back to the server, you're re-running the database query, and need to make changes to the results after it's been run. ColdFusion provides some built-in functions to alter a query object [1]. You would probably be interested in queryAddRow() which adds one or more rows to an existing query, and querySetCell() which can set the value of a specific column in a specific row. There is also the query of queries feature which will allow you to use the CFQUERY tag to select sub-sets of existing query objects [2]. Ben Nadel wrote a custom tag [3] which provides a SQL-like method for updating existing query objects in a more convenient manner than running a bunch of querySetCell() functions. In your case with shared hosting that may not be an option, however. I hope this can get you going in the right direction though. -Justin Scott [1] http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec1a60c-7ffc.html#WSc3ff6d0ea77859461172e0811cbec22c24-67fe [2] http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html [3] http://www.bennadel.com/blog/1707-Running-UPDATE-And-DELETE-SQL-Statements-Using-ColdFusion-Query-Of-Queries.htm ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348491 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Dynamic query help.
I'm writing an app. for an auto repair shop that has a Flash cfgrid containing quantity, description, price and total cost (the quantity times the price). Right now, the initial total cost is being calculated in a view in the database. I've created a dynamic query (using queryNew) to hold all of the initial data from the database and it's creating and populating the dynamic query just fine. I need to be able to have the dynamic query updated as the cfgrid is edited. (for instance if the quantity or price is changed or if rows are added or removed) Then I plan on refreshing the grid using an onChange event to update the grid with the new dynamic query data which would give me the new total costs. Normally I would use one of the existing scripts out there to do all of this for me, but although the grid is in Flash format, the form itself isn't and can't be because of the design layout of the page. (in fact, I tried using the Adding the values of a cfgrid column technique posted on ASFusion website, but it will only work if I use a Flash form which I can't do for this client) Also, this is currently being hosted via shared hosting, so when I try to make the Flash grid an html grid, I get a permissions error. (shared hosting locks out some action script commands such as CreateObject, etc.) I know how to write queries to update a physical database table, but I'm not sure how to update the dynamic query. (I can't use the standard cfquery command because it requires a datasource and will only update a physical database table) Can anyone please help me out here? I've been trying to resolve this for 4 days now and my client is getting impatient. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348473 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Query of query help
Hi all, hoping for some advice with querying a query - at least I think that's what's a good fit here but feel free to advise otherwise. I've got a folder which contains 35,000 folders. I've also got a table with lots of records, where the roomid column matches the name of one of the folders. I know some folders do not have a matching record, and I want to identify those records. cfdirectory action=list name=roomList type=dir directory=#roomsDir# cfquery datasource=#dsn# name=roomsDB SELECT roomid from rooms /cfquery I *think* I need to do something along these lines: cfquery name=orphanRooms dbtype=query select * from roomList WHERE name ... /cfquery But somehow I cannot figure out the correct syntax. The resultset from orphanRooms should contain all the records that are in roomList but not in roomsDB. Any help appreciated. Stefan ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341722 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of query help
Um, did you try the where clause? You said it didn't work - how did it not work? Can you show us the full code and the error? On Mon, Jan 31, 2011 at 11:48 AM, Stefan Richter ste...@flashcomguru.com wrote: Hi all, hoping for some advice with querying a query - at least I think that's what's a good fit here but feel free to advise otherwise. I've got a folder which contains 35,000 folders. I've also got a table with lots of records, where the roomid column matches the name of one of the folders. I know some folders do not have a matching record, and I want to identify those records. cfdirectory action=list name=roomList type=dir directory=#roomsDir# ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341724 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
re: Query of query help
Yes, QoQ will work here. !--- get your folders --- cfdirectory action=list name=roomList type=dir directory=#roomsDir# !--- get the rooms from the DB --- cfquery datasource=#dsn# name=roomsDB SELECT roomid FROM rooms /cfquery cfoutput query=roomList !--- loop through the folders and check against the rooms in the DB --- cfquery dbtype=query name=orphanRoom SELECT name FROM roomsDB WHERE roomid = '#name#' /cfquery cfif not orphanRoom.recordCount !--- flag this as a room without a folder --- /cfif /cfoutput ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341725 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of query help
I've got a folder which contains 35,000 folders. Setting aside your QoQ question, you may have problems simply traversing a folder with this many items in it. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341726 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of query help
Thanks. Guess what I was trying to avoid was a loop with 35,000 queries... I was trying to come up wit a clever way to do this in one SQL statement. One issue I noticed is that QoQ does not to seem to (easily?) support joins. Then again I'm no SQL ninja by any means. S On 31 Jan 2011, at 17:54, Jason Fisher wrote: Yes, QoQ will work here. !--- get your folders --- cfdirectory action=list name=roomList type=dir directory=#roomsDir# !--- get the rooms from the DB --- cfquery datasource=#dsn# name=roomsDB SELECT roomid FROM rooms /cfquery cfoutput query=roomList !--- loop through the folders and check against the rooms in the DB --- cfquery dbtype=query name=orphanRoom SELECT name FROM roomsDB WHERE roomid = '#name#' /cfquery cfif not orphanRoom.recordCount !--- flag this as a room without a folder --- /cfif /cfoutput ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341732 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of query help
Thanks Dave, listing the folders performs a lot better than I expected, so that's not my issue here. Cheers Stefan On 31 Jan 2011, at 17:57, Dave Watts wrote: I've got a folder which contains 35,000 folders. Setting aside your QoQ question, you may have problems simply traversing a folder with this many items in it. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341733 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of query help
Stefan, This might work ... can't remember if the IN () function works in QoQ or not. !--- get a list of all the rooms from the DB (after you query) --- cfset roomList = valueList(roomsDB.roomid) / !--- now query to get folders that are not in the rooms DB list --- cfquery dbtype=query name=roomsOK SELECT * FROM roomList WHERE name NOT IN ( cfqueryparam cfsqltype=cf_sql_varchar value=#roomList# list=true / ) /cfquery From: Stefan Richter ste...@flashcomguru.com Sent: Monday, January 31, 2011 2:42 PM To: cf-talk cf-talk@houseoffusion.com Subject: Re: Query of query help Thanks. Guess what I was trying to avoid was a loop with 35,000 queries... I was trying to come up wit a clever way to do this in one SQL statement. One issue I noticed is that QoQ does not to seem to (easily?) support joins. Then again I'm no SQL ninja by any means. S On 31 Jan 2011, at 17:54, Jason Fisher wrote: Yes, QoQ will work here. !--- get your folders --- cfdirectory action=list name=roomList type=dir directory=#roomsDir# !--- get the rooms from the DB --- cfquery datasource=#dsn# name=roomsDB SELECT roomid FROM rooms /cfquery cfoutput query=roomList !--- loop through the folders and check against the rooms in the DB --- cfquery dbtype=query name=orphanRoom SELECT name FROM roomsDB WHERE roomid = '#name#' /cfquery cfif not orphanRoom.recordCount !--- flag this as a room without a folder --- /cfif /cfoutput ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341735 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of query help
I was trying something like this: cfquery name=orphanRooms dbtype=query select * from roomList WHERE name = 'x' cfloop query=roomsDBOR roomList.name = '#roomid#' /cfloop /cfquery I think I am getting somewhere with it. Anything inherently wrong with this? Stefan On 31 Jan 2011, at 17:54, Raymond Camden wrote: Um, did you try the where clause? You said it didn't work - how did it not work? Can you show us the full code and the error? On Mon, Jan 31, 2011 at 11:48 AM, Stefan Richter ste...@flashcomguru.com wrote: Hi all, hoping for some advice with querying a query - at least I think that's what's a good fit here but feel free to advise otherwise. I've got a folder which contains 35,000 folders. I've also got a table with lots of records, where the roomid column matches the name of one of the folders. I know some folders do not have a matching record, and I want to identify those records. cfdirectory action=list name=roomList type=dir directory=#roomsDir# ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341737 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of query help
Ah great, an interesting approach. Many thanks. Stefan On 31 Jan 2011, at 20:10, Jason Fisher wrote: Stefan, This might work ... can't remember if the IN () function works in QoQ or not. !--- get a list of all the rooms from the DB (after you query) --- cfset roomList = valueList(roomsDB.roomid) / !--- now query to get folders that are not in the rooms DB list --- cfquery dbtype=query name=roomsOK SELECT * FROM roomList WHERE name NOT IN ( cfqueryparam cfsqltype=cf_sql_varchar value=#roomList# list=true / ) /cfquery ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341738 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of query help
Nope, nothing wrong with it. There may be a limit on the number of statements in a QoQ WHERE clause, though I don't know. I know the old Microsoft JET database engine used to have a logical limit of 40 statements in the WHERE clause. Just something to note if it bombs on 30,000 statements ... From: Stefan Richter ste...@flashcomguru.com Sent: Monday, January 31, 2011 3:21 PM To: cf-talk cf-talk@houseoffusion.com Subject: Re: Query of query help I was trying something like this: cfquery name=orphanRooms dbtype=query select * from roomList WHERE name = 'x' cfloop query=roomsDBOR roomList.name = '#roomid#' /cfloop /cfquery I think I am getting somewhere with it. Anything inherently wrong with this? Stefan ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341740 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Aggregate query help please
Hi Barney Your answer was cut - would you mind resending please ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324690 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Aggregate query help please
I have no idea what that was. My sent mail has the same blank message, but I didn't reply. WITH ROLLUP is the clause you want though, if your DB supports it. cheers, barneyb -- Barney Boisvert bboisv...@gmail.com http://www.barneyb.com/ On Jul 17, 2009, at 4:09 PM, Seamus Campbell coldfus...@boldacious.com wrote: Hi Barney Your answer was cut - would you mind resending please ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324691 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Aggregate query help please
I've got some code that uses the JExcelAPI for converting queries to Excel spreadsheets. It's got some support for formulas, so theoretically you could wow the people you need to send it to by not only having the sums there, but having them update if they change the numbers in the various columns. Here's an example of its use: var workbook = variables.workbook; var sheet = workbook.createSheet(Test Sheet); var results = ; sheet.addFormula(evaluateat=row,columnname=Net,formula=sum(amt[currentrow])-((sum(amt[currentrow]) * 5) / 100)); sheet.setQuery(runQuery(SELECT *,'' as overhead FROM creadittransactions LIMIT 19,myDSN)); workbook.writeXLSFile(./test.xls); results = sheet.getQuery(); debug(results); Dunno if an approach like that would be easier or harder, but, well, it's yours for the asking. -- Permanence, perseverance and persistence in spite of all obstacle s, discouragement s, and impossibilities: It is this, that in all things distinguishes the strong soul from the weak. Thomas Carlyle On Thu, Jul 16, 2009 at 5:57 AM, Seamus Campbellcoldfus...@boldacious.com wrote: I have a database (access at the moment) for members of an organisation. The members are scattered throughout (mostly in one state) Australia (with a couple in the US) They all are one of three language groups. I need to export an excel spreadsheet with a count of members from each language group per town, region, state, country plus total count . Then another excel spreadsheet with the count per language group for the end of each financial year. (plus total here as well) I am just at my wits end trying to work this out. I have to have it ready in the next few days. Can anyone help - I think I'd need to forward the relevant tables and the spreadsheets to show the format needed. I'm willing to pay for someone to help me with this. (I presume that it's only 1-2 hours work for someone who knows what they are doing) Many thanks Seamus ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324692 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Aggregate query help please
I have a database (access at the moment) for members of an organisation. The members are scattered throughout (mostly in one state) Australia (with a couple in the US) They all are one of three language groups. I need to export an excel spreadsheet with a count of members from each language group per town, region, state, country plus total count . Then another excel spreadsheet with the count per language group for the end of each financial year. (plus total here as well) I am just at my wits end trying to work this out. I have to have it ready in the next few days. Can anyone help - I think I'd need to forward the relevant tables and the spreadsheets to show the format needed. I'm willing to pay for someone to help me with this. (I presume that it's only 1-2 hours work for someone who knows what they are doing) Many thanks Seamus ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324583 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Aggregate query help please
On Thu, Jul 16, 2009 at 4:57 AM, Seamus Campbellcoldfus...@boldacious.com wrote: I have a database (access at the moment) for members of an organisation. The members are scattered throughout (mostly in one state) Australia (with a couple in the US) They all are one of three language groups. I need to export an excel spreadsheet with a count of members from each language group per town, region, state, country plus total count . Then another excel spreadsheet with the count per language group for the end of each financial year. (plus total here as well) I am just at my wits end trying to work this out. I have to have it ready in the next few days. Can anyone help - I think I'd need to forward the relevant tables and the spreadsheets to show the format needed. I'm willing to pay for someone to help me with this. (I presume that it's only 1-2 hours work for someone who knows what they are doing) Many thanks Seamus ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324593 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
Cool, yeah I never remember until I do it when an aggregate query is going to want HAVING vs WHERE. Glad it's working for you! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316379 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
- Original Message - From: Jason Fisher [EMAIL PROTECTED] Cool, yeah I never remember until I do it when an aggregate query is going to want HAVING vs WHERE. Glad it's working for you! This bites me too when I'm not paying attention. Just remember that the WHERE applies to the records BEFORE the are grouped up and the HAVING applies to the grouped result set after the aggregates have been applied. ~Brad ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316384 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
In MS SQL Server it's ISNULL(), but can't speak for other platforms. Can't recall what it is in Oracle, might just be NULL(). ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316319 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
In MS SQL Server it's ISNULL(), but can't speak for other platforms. Can't recall what it is in Oracle, might just be NULL(). IIRC in Oracle it is NVL. There is also COALESCE, which is usually a safe bet with most databases. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316349 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: sql query help
didn't work, got an error. changed the isnull to ifnull, got a invalid use of a group function error... I don't even know how to fix that...=( Try this, I think it's what you're looking for: SELECT SUM(ISNULL(o.qty, 0)) as sold, p.sku, p.name, p.points, p. short_description, p.quantity, p.image FROM tblproducts as p LEFT JOIN tblorder_list as o ON p.sku = o.sku #can_afford# WHERE SUM(ISNULL(o.qty, 0)) p.quantity GROUP BY p.sku ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316372 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
NM, got it... changed the where clause to having and moved it below the group by... seems to be working so far! Thanks! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316373 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
sql query help
I have a MySQL query to pull all products from the database, no problem. I am trying to get it to only display prodcuts that are in stock, the value should be sold p.quantity in the following query. however, when sold appears as null (very often) it removes the full record, which I don't want. there has to be an easy way to do this... Here is my current query: cfquery name=rsproducts datasource=#application.db# SELECT SUM(o.qty) as sold, p.sku, p.name, p.points, p.short_description, p.quantity, p.image FROM tblproducts as p LEFT JOIN tblorder_list as o ON p.sku=o.sku #can_afford# GROUP BY p.sku /cfquery Thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316295 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
Try this, I think it's what you're looking for: SELECT SUM(ISNULL(o.qty, 0)) as sold, p.sku, p.name, p.points, p.short_description, p.quantity, p.image FROM tblproducts as p LEFT JOIN tblorder_list as o ON p.sku = o.sku #can_afford# WHERE SUM(ISNULL(o.qty, 0)) p.quantity GROUP BY p.sku ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316300 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
i believe the correct function to use is IFNULL(), not ISNULL()... Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Jason Fisher wrote: Try this, I think it's what you're looking for: SELECT SUM(ISNULL(o.qty, 0)) as sold, p.sku, p.name, p.points, p.short_description, p.quantity, p.image FROM tblproducts as p LEFT JOIN tblorder_list as o ON p.sku = o.sku #can_afford# WHERE SUM(ISNULL(o.qty, 0)) p.quantity GROUP BY p.sku ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316311 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: query help
no luck... I do need the group by clause, my actual query is much larger than the one posted. the problem, for example is this: for five people, paid=no, for five others, paid=yes under one parent sponsor. I am now getting a result of 10, but I need the result to be 5, only counting those people under sponsor who have paid. what am I doing wrong?? =( ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307864 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: query help
Hard to say what is going on. The following works fine for me: SELECT node.userCity,count(node.userCity) FROM core.users AS node, core.users AS parent WHERE parent.userName = node.userFirstName AND node.userCity = 'Pasadena' GROUP BY node.userCity I seem to get the correct number based on the node.userCity where clause. On Fri, Jun 20, 2008 at 4:23 PM, Jessica Kennedy [EMAIL PROTECTED] wrote: no luck... I do need the group by clause, my actual query is much larger than the one posted. the problem, for example is this: for five people, paid=no, for five others, paid=yes under one parent sponsor. I am now getting a result of 10, but I need the result to be 5, only counting those people under sponsor who have paid. what am I doing wrong?? =( ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307865 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
query help
I'm pretty sure I will smack my head when I hear the answer, but I'll ask anyway... I am using a select count() query to get the number of people directly sponsored by a person... this works fine, the problem is that I only want to have the query count people that meet a certain qualification. There has to be a simple way to do this, my query pretty much looks like this: SELECT COUNT(node.name) FROM tbl1 as node, tbl1 as parent WHERE parent.sponsor=node.name AND node.paid='yes' GROUP BY node.name the AND node.paid='yes' seems to be doing nothing... I'm pretty sure I'm missing something really simple, help! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307630 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: query help
Well, is the paid field a character field or a number field in the DB? and just FYI, you can always go #querryName.Recordcount# to get a count via CF. :) On Tue, Jun 17, 2008 at 3:23 PM, Jessica Kennedy [EMAIL PROTECTED] wrote: I'm pretty sure I will smack my head when I hear the answer, but I'll ask anyway... I am using a select count() query to get the number of people directly sponsored by a person... this works fine, the problem is that I only want to have the query count people that meet a certain qualification. There has to be a simple way to do this, my query pretty much looks like this: SELECT COUNT(node.name) FROM tbl1 as node, tbl1 as parent WHERE parent.sponsor=node.name AND node.paid='yes' GROUP BY node.name the AND node.paid='yes' seems to be doing nothing... I'm pretty sure I'm missing something really simple, help! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307632 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: query help
SELECT COUNT(node.name) FROM tbl1 as node, tbl1 as parent WHERE parent.sponsor=node.name AND node.paid='yes' GROUP BY node.name GROUP BY should only be used if you're selecting non-aggregates along with your aggregate. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307633 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SOT: MSSQL Query help - Finding an exact word in a field
We are trying to tease out the following: SELECT ProjNum, ClientName, tblProj.Title, StartDate FROM tblProj WHERE (tblProj.Title LIKE '%Cost Plan%') OR(tblProj.Title LIKE '%Cost Allocation Plan%') OR (tblProj.Title LIKE '%CAP%') The problem is that OR (tblProj.Title LIKE '%CAP%') pulls out everything with CAP in it, like captital, captain etc. (as it should). What we want is to get everything with just the word CAP in it and not word that contains CAP. I know it can be done using Full-Text Search but we don't have it enabled on the DB and installing it is not going to happen any time soon. And this is some what time urgent. Does anyone know how to do that with out enabling Full-Text Search on SQL Server? TIA G We learn something every day, and lots of times it's that what we learned the day before was wrong. - Bill Vaughan ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305831 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: MSSQL Query help - Finding an exact word in a field
Are there a fixed set of delimiters in use in your title field? If so then you could look for: (Title = 'CAP' OR Title like 'CAP %' OR Title like '% CAP %' OR Title like '% CAP') -Original Message- From: Gerald Guido [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 1:43 PM To: CF-Talk Subject: SOT: MSSQL Query help - Finding an exact word in a field We are trying to tease out the following: SELECT ProjNum, ClientName, tblProj.Title, StartDate FROM tblProj WHERE (tblProj.Title LIKE '%Cost Plan%') OR(tblProj.Title LIKE '%Cost Allocation Plan%') OR (tblProj.Title LIKE '%CAP%') The problem is that OR (tblProj.Title LIKE '%CAP%') pulls out everything with CAP in it, like captital, captain etc. (as it should). What we want is to get everything with just the word CAP in it and not word that contains CAP. I know it can be done using Full-Text Search but we don't have it enabled on the DB and installing it is not going to happen any time soon. And this is some what time urgent. Does anyone know how to do that with out enabling Full-Text Search on SQL Server? TIA G We learn something every day, and lots of times it's that what we learned the day before was wrong. - Bill Vaughan ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305835 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: MSSQL Query help - Finding an exact word in a field
I know you can use square brackets and the carrot symbol to exclude characters. LIKE 'CAP[^T]%' Will give you everything that does not begin with CAPT So I _think_ you can do this. Try it and let me know. LIKE '%[^abcdefghijklmnopqrstuvwxyz]CAP[^abcdefghijklmnopqrstuvwxyz]%' You are probably going to have to look at the records as you are outputting them and toss out the bad ones so they don't display. Are you sure you cant turn FTS on? It is sooo much faster than LIKE. -Original Message- From: Gerald Guido [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 1:43 PM To: CF-Talk Subject: SOT: MSSQL Query help - Finding an exact word in a field We are trying to tease out the following: SELECT ProjNum, ClientName, tblProj.Title, StartDate FROM tblProj WHERE (tblProj.Title LIKE '%Cost Plan%') OR(tblProj.Title LIKE '%Cost Allocation Plan%') OR (tblProj.Title LIKE '%CAP%') The problem is that OR (tblProj.Title LIKE '%CAP%') pulls out everything with CAP in it, like captital, captain etc. (as it should). What we want is to get everything with just the word CAP in it and not word that contains CAP. I know it can be done using Full-Text Search but we don't have it enabled on the DB and installing it is not going to happen any time soon. And this is some what time urgent. Does anyone know how to do that with out enabling Full-Text Search on SQL Server? TIA G We learn something every day, and lots of times it's that what we learned the day before was wrong. - Bill Vaughan ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305836 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: MSSQL Query help - Finding an exact word in a field
In MySQL... '%CAP %' works. -Original Message- From: Gerald Guido [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 12:43 PM To: CF-Talk Subject: SOT: MSSQL Query help - Finding an exact word in a field We are trying to tease out the following: SELECT ProjNum, ClientName, tblProj.Title, StartDate FROM tblProj WHERE (tblProj.Title LIKE '%Cost Plan%') OR(tblProj.Title LIKE '%Cost Allocation Plan%') OR (tblProj.Title LIKE '%CAP%') The problem is that OR (tblProj.Title LIKE '%CAP%') pulls out everything with CAP in it, like captital, captain etc. (as it should). What we want is to get everything with just the word CAP in it and not word that contains CAP. I know it can be done using Full-Text Search but we don't have it enabled on the DB and installing it is not going to happen any time soon. And this is some what time urgent. Does anyone know how to do that with out enabling Full-Text Search on SQL Server? TIA G We learn something every day, and lots of times it's that what we learned the day before was wrong. - Bill Vaughan ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305837 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: MSSQL Query help - Finding an exact word in a field
I re-read my post and I don't make any sense. I think I was typing faster than my brain was working. :) LIKE 'CAP[^T]%' Will not return records with CAPT at the beginning. So LIKE '%[^abcdefghijklmnopqrstuvwxyz]CAP[^abcdefghijklmnopqrstuvwxyz]%' Will return records with CAP or 8CAP9, but won't return records with CAPTAIN or CAPITAL. At least I think it will. Try it out. I am not in front of a MS SQL server. -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 2:17 PM To: CF-Talk Subject: RE: MSSQL Query help - Finding an exact word in a field I know you can use square brackets and the carrot symbol to exclude characters. LIKE 'CAP[^T]%' Will give you everything that does not begin with CAPT So I _think_ you can do this. Try it and let me know. LIKE '%[^abcdefghijklmnopqrstuvwxyz]CAP[^abcdefghijklmnopqrstuvwxyz]%' You are probably going to have to look at the records as you are outputting them and toss out the bad ones so they don't display. Are you sure you cant turn FTS on? It is sooo much faster than LIKE. -Original Message- From: Gerald Guido [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 1:43 PM To: CF-Talk Subject: SOT: MSSQL Query help - Finding an exact word in a field We are trying to tease out the following: SELECT ProjNum, ClientName, tblProj.Title, StartDate FROM tblProj WHERE (tblProj.Title LIKE '%Cost Plan%') OR(tblProj.Title LIKE '%Cost Allocation Plan%') OR (tblProj.Title LIKE '%CAP%') The problem is that OR (tblProj.Title LIKE '%CAP%') pulls out everything with CAP in it, like captital, captain etc. (as it should). What we want is to get everything with just the word CAP in it and not word that contains CAP. I know it can be done using Full-Text Search but we don't have it enabled on the DB and installing it is not going to happen any time soon. And this is some what time urgent. Does anyone know how to do that with out enabling Full-Text Search on SQL Server? TIA G We learn something every day, and lots of times it's that what we learned the day before was wrong. - Bill Vaughan ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305839 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: MSSQL Query help - Finding an exact word in a field
I am full of incomplete thoughts. I _think_ you can abbreviate with a-z and 1-9 instead of typing out the characters. -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 2:27 PM To: CF-Talk Subject: RE: MSSQL Query help - Finding an exact word in a field I re-read my post and I don't make any sense. I think I was typing faster than my brain was working. :) LIKE 'CAP[^T]%' Will not return records with CAPT at the beginning. So LIKE '%[^abcdefghijklmnopqrstuvwxyz]CAP[^abcdefghijklmnopqrstuvwxyz]%' Will return records with CAP or 8CAP9, but won't return records with CAPTAIN or CAPITAL. At least I think it will. Try it out. I am not in front of a MS SQL server. -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 2:17 PM To: CF-Talk Subject: RE: MSSQL Query help - Finding an exact word in a field I know you can use square brackets and the carrot symbol to exclude characters. LIKE 'CAP[^T]%' Will give you everything that does not begin with CAPT So I _think_ you can do this. Try it and let me know. LIKE '%[^abcdefghijklmnopqrstuvwxyz]CAP[^abcdefghijklmnopqrstuvwxyz]%' You are probably going to have to look at the records as you are outputting them and toss out the bad ones so they don't display. Are you sure you cant turn FTS on? It is sooo much faster than LIKE. -Original Message- From: Gerald Guido [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 1:43 PM To: CF-Talk Subject: SOT: MSSQL Query help - Finding an exact word in a field We are trying to tease out the following: SELECT ProjNum, ClientName, tblProj.Title, StartDate FROM tblProj WHERE (tblProj.Title LIKE '%Cost Plan%') OR(tblProj.Title LIKE '%Cost Allocation Plan%') OR (tblProj.Title LIKE '%CAP%') The problem is that OR (tblProj.Title LIKE '%CAP%') pulls out everything with CAP in it, like captital, captain etc. (as it should). What we want is to get everything with just the word CAP in it and not word that contains CAP. I know it can be done using Full-Text Search but we don't have it enabled on the DB and installing it is not going to happen any time soon. And this is some what time urgent. Does anyone know how to do that with out enabling Full-Text Search on SQL Server? TIA G We learn something every day, and lots of times it's that what we learned the day before was wrong. - Bill Vaughan ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305840 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MSSQL Query help - Finding an exact word in a field
Chad, we got that to work with some tweaks. It was skipping stuff where CAP as the first word or last word. But yeah that did the trick. Thanx to everyone that helped! You helped save the day. G$ On Wed, May 21, 2008 at 2:29 PM, Chad Gray [EMAIL PROTECTED] wrote: I am full of incomplete thoughts. I _think_ you can abbreviate with a-z and 1-9 instead of typing out the characters. -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 2:27 PM To: CF-Talk Subject: RE: MSSQL Query help - Finding an exact word in a field I re-read my post and I don't make any sense. I think I was typing faster than my brain was working. :) LIKE 'CAP[^T]%' Will not return records with CAPT at the beginning. So LIKE '%[^abcdefghijklmnopqrstuvwxyz]CAP[^abcdefghijklmnopqrstuvwxyz]%' Will return records with CAP or 8CAP9, but won't return records with CAPTAIN or CAPITAL. At least I think it will. Try it out. I am not in front of a MS SQL server. -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 2:17 PM To: CF-Talk Subject: RE: MSSQL Query help - Finding an exact word in a field I know you can use square brackets and the carrot symbol to exclude characters. LIKE 'CAP[^T]%' Will give you everything that does not begin with CAPT So I _think_ you can do this. Try it and let me know. LIKE '%[^abcdefghijklmnopqrstuvwxyz]CAP[^abcdefghijklmnopqrstuvwxyz]%' You are probably going to have to look at the records as you are outputting them and toss out the bad ones so they don't display. Are you sure you cant turn FTS on? It is sooo much faster than LIKE. -Original Message- From: Gerald Guido [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 1:43 PM To: CF-Talk Subject: SOT: MSSQL Query help - Finding an exact word in a field We are trying to tease out the following: SELECT ProjNum, ClientName, tblProj.Title, StartDate FROM tblProj WHERE (tblProj.Title LIKE '%Cost Plan%') OR(tblProj.Title LIKE '%Cost Allocation Plan%') OR (tblProj.Title LIKE '%CAP%') The problem is that OR (tblProj.Title LIKE '%CAP%') pulls out everything with CAP in it, like captital, captain etc. (as it should). What we want is to get everything with just the word CAP in it and not word that contains CAP. I know it can be done using Full-Text Search but we don't have it enabled on the DB and installing it is not going to happen any time soon. And this is some what time urgent. Does anyone know how to do that with out enabling Full-Text Search on SQL Server? TIA G We learn something every day, and lots of times it's that what we learned the day before was wrong. - Bill Vaughan ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305841 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Query Help - include
I am using Oracle and I have a table for expenditures that stores a person's ID when the expenditures is approved. The person's ID is from a People table. When I query, I want to make a new variable that references the other table to put in their full name. I have two problems doing this. 1 - I'm not so good at sql and the join only works if there's an id in the approved_by field. This is going to be empty unless the ticket has been approved, but I still want it to work whether it's approved or not. Is that an outter join? 2. Currently, I do p.fname AS approved_by_fname,p.fname AS approved_by_lname. I'd like it to be one variable though, but I'm not sure how to format p.fname + p.lname AS approved_by_name. Here's my current query: CFQUERY NAME=getSelect DATASOURCE=dh debug=yes result=the_result SELECT e.expenditure_id,e.date_added,e.date_modified,e.approval,e.purpose,e.estimate,e.department,e.department_other, e.vendor_name,e.vendor_address,e.vendor_phone,e.vendor_contact,e.fei,e.state_employee,e.foundation,e.frs, e.principal_investigator,e.requestor_fname,e.requestor_lname,e.requestor_email,e.approved_by,e.approval_date,p.fname AS approved_by_fname,p.fname AS approved_by_lname FROM expenditures e, people p WHERE e.approved_by = p.id AND date_added = #url.start_date# AND date_added = #url.end_date# cfswitch expression=#URL.approval# cfcase value=pending AND approval = 'Pending' /cfcase cfcase value=approved AND approval = 'Approved' /cfcase cfcase value=denied AND approval = 'Denied' /cfcase cfcase value=all !--- no WHERE needed - it's all of the tickets --- /cfcase /cfswitch ORDER BY #the_sort# /CFQUERY ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304979 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Help - include
daniel kessler wrote: 1 - I'm not so good at sql and the join only works if there's an id in the approved_by field. This is going to be empty unless the ticket has been approved, but I still want it to work whether it's approved or not. Is that an outter join? Yes it will be an LEFT or RIGHT outer join. The direction determines which table you want ALL records from. So if the expense table is on the left of the join use expense LEFT OUTER JOIN people on aField = bField. If yo want it on the right then it would be people RIGHT OTER JOIN expense on aField = bField. 2. Currently, I do p.fname AS approved_by_fname,p.fname AS approved_by_lname. I'd like it to be one variable though, but I'm not sure how to format p.fname + p.lname AS approved_by_name. In Oracle '||' is the concatenate operator so it would be p.fname || ' ' || p.lname AS approved_by_name. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304982 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Help - include
I had continued to try and figure it out after I sent the email. I did figure out a way to make it work. FROM expenditures e, people p WHERE e.approved_by = people.id (+) In Oracle, the + does the outer join. And it worked well, but it wasn't explicit. I don't know sql well enough to read that alter and go, oh, I'm doing an outer join there. I wanted explicit and that's what your code gave me. Interestingly, it happens in the FROM rather than the WHERE cause of the ON. thank you. Yes it will be an LEFT or RIGHT outer join. The direction determines which table you want ALL records from. So if the expense table is on the left of the join use expense LEFT OUTER JOIN people on aField = bField. If yo want it on the right then it would be people RIGHT OTER JOIN expense on aField = bField. Thank you too for the explanation. It was helpful. And the concatenation worked great too. daniel ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304988 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Help - include
daniel kessler wrote: I had continued to try and figure it out after I sent the email. I did figure out a way to make it work. FROM expenditures e, people p WHERE e.approved_by = people.id (+) In Oracle, the + does the outer join. Yes, that is the original outer join syntax for Oracle. The ... OUTER JOIN ... ON ... syntax is the SQL 92 Standard syntax. Oracle didn't adopt this standard until Version 9.x IIRC. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304990 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Help Please
select *, exists ( select * from orders where date between #date1# and #date2# ) as hasOrdered from customers order by name, id Use CFQUERYPARAM, of course. Another variation is a left join. Ditto on using cfqueryparam. SELECT c.ID, c.Name, c.Email, SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound FROMtblCustomers AS c LEFT JOIN tblOrders AS o ON c.ID = o.UserID AND o.Date BETWEEN @startDate AND @endDate GROUP BY c.ID, c.Name, c.Email ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304381 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Help Please
I dont believe this is working with Access DB SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound C S [EMAIL PROTECTED] wrote: select *, exists ( select * from orders where date between #date1# and #date2# ) as hasOrdered from customers order by name, id Use CFQUERYPARAM, of course. Another variation is a left join. Ditto on using cfqueryparam. SELECT c.ID, c.Name, c.Email, SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound FROM tblCustomers AS c LEFT JOIN tblOrders AS o ON c.ID = o.UserID AND o.Date BETWEEN @startDate AND @endDate GROUP BY c.ID, c.Name, c.Email ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304382 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Help Please
I dont believe this is working with Access DB SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound I think Access's version of CASE is IIF(..). Try using IIF instead and also try the query Barney suggested. One of them should work. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304392 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Query Help Please
I will try to make this as simple as possible. Any help would be greatly appreciated. I have 2 tables like below. tblCustomers === ID | Name | Email | 1, John, [EMAIL PROTECTED] 2, Bob, [EMAIL PROTECTED] 3, Steve, [EMAIL PROTECTED] === tblOrders === UserID | Date| 1, 12/22/2007 3, 1/2/2008 === I want to do a query like a scorecard view, that will list all CUSTOMERS and show yes or no if they had an order bewtween 2 dates. RESULTS WOULD BE LIKE THIS IF I SEARCH BETWEEN 12/01/2007 - 1/30/2008 - ID | Name | Email | 1, John, YES 2, Bob, NO 3, Steve, YES ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304374 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query Help Please
select *, exists ( select * from orders where date between #date1# and #date2# ) as hasOrdered from customers order by name, id Use CFQUERYPARAM, of course. cheers, barneyb On Mon, Apr 28, 2008 at 8:57 PM, Brian Sheridan [EMAIL PROTECTED] wrote: I will try to make this as simple as possible. Any help would be greatly appreciated. I have 2 tables like below. tblCustomers === ID | Name | Email | 1, John, [EMAIL PROTECTED] 2, Bob, [EMAIL PROTECTED] 3, Steve, [EMAIL PROTECTED] === tblOrders === UserID | Date| 1, 12/22/2007 3, 1/2/2008 === I want to do a query like a scorecard view, that will list all CUSTOMERS and show yes or no if they had an order bewtween 2 dates. RESULTS WOULD BE LIKE THIS IF I SEARCH BETWEEN 12/01/2007 - 1/30/2008 - ID | Name | Email | 1, John, YES 2, Bob, NO 3, Steve, YES ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304375 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: query help
Did that work for you? On Mon, Mar 10, 2008 at 12:25 PM, Sonny Savage [EMAIL PROTECTED] wrote: Now that I understand your data structures, I understand how to solve the problem. I hope this works on MySQL. I tested it using OpenOffice.orgBase. SELECT users.user_id , users.user_name , docs.doc_id , docs.doc_name , user_docs.signoff_id FROM users, docs LEFT OUTER JOIN user_docs ON users.user_id = user_docs.user_id AND docs.doc_id = user_docs.doc_id WHERE user_docs.signoff_id IS NULL On Mon, Mar 10, 2008 at 10:01 AM, Josh McKinley [EMAIL PROTECTED] wrote: I think you've made a bit of an error there. With inner joins, the query returns no results. This is what I expected. Using a left outer join also returns no rows. This makes sense as you're maintaining the rows from the wrong table. Changing it to a right outer join creates a cross dependency error. The user table has all of the users. The doc table has all of the docs. The user_doc table has one entry for each time a user has signed off on having read a document. A row is inserted with the user id and the doc id. What I'm trying to return is the users who have not signed off on viewing one or more documents and the documents on which they've failed to sign off. Subject: query help From: Sonny Savage Date: Mon, 10 Mar 2008 08:39:57 -0400 Thread: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55471#300843 Question: Do you also want a result in the case where there is no USER_DOCS record for a given USER and DOC record (FULL OUTER JOIN)? On Sat, Mar 8, 2008 at 12:12 AM, Josh McKinley [EMAIL PROTECTED] wrote: - Excess quoted text cut - see Original Post for more - ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300928 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: query help
Question: Do you also want a result in the case where there is no USER_DOCS record for a given USER and DOC record (FULL OUTER JOIN)? On Sat, Mar 8, 2008 at 12:12 AM, Josh McKinley [EMAIL PROTECTED] wrote: I've got three tables like this (simplified): USERS user_id user_name DOCS doc_id doc_name USER_DOCS signoff_id user_id doc_id I need all user names and doc names where the combination doesn't exist in the signoff table USER_DOCS. For example, user 12 hasn't signed of on doc 9 and user 6 hasn't signed off on doc 44, I need to know that. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300843 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: query help
This query will return all users and docs that have a relationship established but don't have a sign-off record: SELECT u.user_name , d.doc_name FROM user_docs ud INNER JOIN users u ON ud.user_id = u.user_id INNER JOIN docs d ON ud.doc_id = d.doc_id WHERE ud.signoff_id IS NULL It will not return records that have non-matching foreign keys (non-existent user or doc). Converting the INNER JOINS to LEFT OUTER JOINS would change that behavior. On Mon, Mar 10, 2008 at 8:39 AM, Sonny Savage [EMAIL PROTECTED] wrote: Question: Do you also want a result in the case where there is no USER_DOCS record for a given USER and DOC record (FULL OUTER JOIN)? On Sat, Mar 8, 2008 at 12:12 AM, Josh McKinley [EMAIL PROTECTED] wrote: I've got three tables like this (simplified): USERS user_id user_name DOCS doc_id doc_name USER_DOCS signoff_id user_id doc_id I need all user names and doc names where the combination doesn't exist in the signoff table USER_DOCS. For example, user 12 hasn't signed of on doc 9 and user 6 hasn't signed off on doc 44, I need to know that. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300844 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: query help
I think you've made a bit of an error there. With inner joins, the query returns no results. This is what I expected. Using a left outer join also returns no rows. This makes sense as you're maintaining the rows from the wrong table. Changing it to a right outer join creates a cross dependency error. The user table has all of the users. The doc table has all of the docs. The user_doc table has one entry for each time a user has signed off on having read a document. A row is inserted with the user id and the doc id. What I'm trying to return is the users who have not signed off on viewing one or more documents and the documents on which they've failed to sign off. Subject: query help From: Sonny Savage Date: Mon, 10 Mar 2008 08:39:57 -0400 Thread: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55471#300843 Question: Do you also want a result in the case where there is no USER_DOCS record for a given USER and DOC record (FULL OUTER JOIN)? On Sat, Mar 8, 2008 at 12:12 AM, Josh McKinley [EMAIL PROTECTED] wrote: - Excess quoted text cut - see Original Post for more - ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300853 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: query help
Now that I understand your data structures, I understand how to solve the problem. I hope this works on MySQL. I tested it using OpenOffice.orgBase. SELECT users.user_id , users.user_name , docs.doc_id , docs.doc_name , user_docs.signoff_id FROM users, docs LEFT OUTER JOIN user_docs ON users.user_id = user_docs.user_id AND docs.doc_id = user_docs.doc_id WHERE user_docs.signoff_id IS NULL On Mon, Mar 10, 2008 at 10:01 AM, Josh McKinley [EMAIL PROTECTED] wrote: I think you've made a bit of an error there. With inner joins, the query returns no results. This is what I expected. Using a left outer join also returns no rows. This makes sense as you're maintaining the rows from the wrong table. Changing it to a right outer join creates a cross dependency error. The user table has all of the users. The doc table has all of the docs. The user_doc table has one entry for each time a user has signed off on having read a document. A row is inserted with the user id and the doc id. What I'm trying to return is the users who have not signed off on viewing one or more documents and the documents on which they've failed to sign off. Subject: query help From: Sonny Savage Date: Mon, 10 Mar 2008 08:39:57 -0400 Thread: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55471#300843 Question: Do you also want a result in the case where there is no USER_DOCS record for a given USER and DOC record (FULL OUTER JOIN)? On Sat, Mar 8, 2008 at 12:12 AM, Josh McKinley [EMAIL PROTECTED] wrote: - Excess quoted text cut - see Original Post for more - ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300859 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: query help
The nice thing about SQL is you can often write the query almost how you word the problem: SELECT user_id,user_name,doc_id,doc_name FROMUSERS,DOCS WHERE NOT EXISTS ( SELECT signoff_id FROMUSER_DOCS WHERE user_id = USERS.user_id AND doc_id = DOCS.doc_id ) Basically, this gets all users and all docs (returning a row for every combination thereof) and then filters out any with a match in USERS_DOCS. As a side note, I generally recommend that you have a compound primary key in a join table. So, USERS_DOCS would have a compound primary key of user_id and doc_id and wouldn't need another column. This helps to enforce referential integrity. Steve I need all user names and doc names where the combination doesn't exist in the signoff table USER_DOCS. For example, user 12 hasn't signed of on doc 9 and user 6 hasn't signed off on doc 44, I need to know that. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300791 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: query help
As a side note, I generally recommend that you have a compound primary key in a join table. So, USERS_DOCS would have a compound primary key of user_id and doc_id and wouldn't need another column. This helps to enforce referential integrity. Or create a unique index / constraint of the two columns and have a surrogate primary key should you need to join a single row to another table. A primary key should not be what makes a row unique, simply a reference to the row for relationship definitions - every table should have at least one unique constraint / index other than the primary key constraint. A simplified example where you might want a surrogate key: Actors (ActorId, Name) Films (FilmId, Name) FilmActor(FilmActorId, FilmId, ActorId) --- surrogate key (FilmActorId) instead of compound pk, unique index on FilmId ActorId FilmActorReview(ReviewId, FilmActorId, ReviewText) My tuppence, Dominic ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300793 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
re: query help
I'm really looking for a solution with only one query using joins. It should be possible to do this with a couple of joins. This query, suggested by Steve, would work well if the tables remained small: SELECT user_id,user_name,doc_id,doc_name FROM USERS,DOCS WHERE NOT EXISTS ( SELECT signoff_id FROM USER_DOCS WHERE user_id = USERS.user_id AND doc_id = DOCS.doc_id) In large tables, though, I don't think it's going to be a very good solution. It seems like something along the lines of this should work: select u.user_name, d.doc_name from docs d left join( users u left join user_docs ud on u.user_id = ud.user_id ) where ud.user_id is null Actually, I just tried that and it does work on my test db, which is mysql. Strangely enough, I tried it on the production db which is SQL Server (tables are different, but relationships are the same) and it doesn't work. Anybody know why this doesn't work in SQL Server 2K??? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300794 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: query help
In large tables, though, I don't think it's going to be a very good solution. Why not? It should perform fine if there are indexes on the tables. Make a couple of test tables and populate them with a whole load of numbers and test it before rejecting it ;) Anybody know why this doesn't work in SQL Server 2K??? It doesn't look syntactically correct (I have only ever used SQL Server); the left join (...) would be causing the problem I think - should be a valid SQL statement selecting a recordset between those parenthesis for the query to work. Another approach would be to change the design of the db slightly. Every user that needs to sign-off a given document would have a user_docs entry and the user_entry table would have a bit flag to say whether the doc has been signed off or not. Then the query to select user docs not signed off is very straightforward: SELECT u.*, d.* FROM user_docs ud INNER JOIN users u ON u.user_id = ud.user_id INNER JOIN docs d ON d.doc_id = ud.doc_id WHERE ud.signed_off = 0 HTH Dominic -- Blog it up: http://fusion.dominicwatson.co.uk ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300796 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: query help
Josh, I use WHERE EXISTS and WHERE NOT EXISTS with some frequency and I have never run into any performance problems with it. In SQL Server, each join statement needs an on clause, even if it is 1 = 1. I ran some quick tests with about 50 rows in one table, 200 in another, and about a dozen in the join table. Despite returning over 10,000 results, neither table took even a second to execute in SQL Server (and that without any indexes). They did, however, return a different number of results. Hope that helps, Steve The syntax I used should work in any database. Your syntax I'm really looking for a solution with only one query using joins. It should be possible to do this with a couple of joins. This query, suggested by Steve, would work well if the tables remained small: SELECT user_id,user_name,doc_id,doc_name FROM USERS,DOCS WHERE NOT EXISTS ( SELECT signoff_id FROM USER_DOCS WHERE user_id = USERS.user_id AND doc_id = DOCS.doc_id) In large tables, though, I don't think it's going to be a very good solution. It seems like something along the lines of this should work: select u.user_name, d.doc_name from docs d left join( users u left join user_docs ud on u.user_id = ud.user_id ) where ud.user_id is null Actually, I just tried that and it does work on my test db, which is mysql. Strangely enough, I tried it on the production db which is SQL Server (tables are different, but relationships are the same) and it doesn't work. Anybody know why this doesn't work in SQL Server 2K??? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300797 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: query help
OK, so let's just say that the where not exists option is comparable in efficiency. I ran the query and was greeted by every row in the tables. 70K results instead of the 60 or so I should be getting. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300798 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: query help
Josh, The SQL I posted worked for me in my tests. If you deviated from that, can you post the SQL that you used? Assuming the join table doesn't have any duplicate rows (and no rows exist in the join table without a match in both of the other tables) then the resultset of the query should have a number of rows equal to the following equation: (number of row in USERS * number of rows in DOCS) - number of rows in USERS_DOCS OK, so let's just say that the where not exists option is comparable in efficiency. I ran the query and was greeted by every row in the tables. 70K results instead of the 60 or so I should be getting. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300801 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
query help
I've got three tables like this (simplified): USERS user_id user_name DOCS doc_id doc_name USER_DOCS signoff_id user_id doc_id I need all user names and doc names where the combination doesn't exist in the signoff table USER_DOCS. For example, user 12 hasn't signed of on doc 9 and user 6 hasn't signed off on doc 44, I need to know that. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300787 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: query help
By the way, this is a MySQL DB. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300789 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: query help
I've never worked in MySQL, but take a look at the below. It should get you in the right direction. BTW, it's a 2 step process. 1st, your combining the User and Docs tables to get a list. Then you take that list and bounce it agaist the USER_DOCS table and show anything where SIGNOFF is NULL. SELECT a.USER_ID, a.USER_NAME, b.DOC_ID, b.DOC_NAME INTO #TEMPTBL FROM USERS a, DOCS b SELECT a.USER_ID, a.USER_NAME, a.DOC_ID, a.DOC_NAME, b.SIGNOFF_ID FROM #TEMPTBL a LEFT JOIN USER_DOCS b ON (a.USER_ID = b.USER_ID) AND ( a.DOC_ID = b.DOC_ID) WHERE b.SIGNOFF_ID is null Good Luck!! - Dae ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300790 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need query help
SELECT t.groupid,
t.groupname
FROM @tblgroups t
INNER JOIN @tblgrouptypes gt ON gt.groupid = t.groupid
WHERE gt.grouptype in ('College','Jewish') -- This would be dynamic
GROUP BY t.groupid,
t.groupname
HAVING count(*) = 2 -- This would be dynamic
ORDER BY t.groupname
~Brad
This looks sweet! I'm havin to move along on his project but will revisit the
query and test it out!
Thanks brad!
Will
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300492
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Need query help
If you query is highly dynamic, this way may not be best. Let's say the user can specify groups that are of type college, jewish, preschool, catholic, and skinny. Now, you have to join to the type table 5 times and that ain't looking too good. ~Brad -Original Message- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2008 6:27 PM To: CF-Talk Subject: Re: Need query help No, that'll get one or the other - he wants ones that are both. Aliasing will work, something like this: James, I think I got this to work. My first tests are good. I didn't mention this is a highly dynamic query. I have quite a few other filters in play, but your example would work nicely with it. Thanks much! Will ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300416 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Need query help
Could do a UNION to gather the results -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 04, 2008 10:56 AM To: CF-Talk Subject: RE: Need query help If you query is highly dynamic, this way may not be best. Let's say the user can specify groups that are of type college, jewish, preschool, catholic, and skinny. Now, you have to join to the type table 5 times and that ain't looking too good. ~Brad -Original Message- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2008 6:27 PM To: CF-Talk Subject: Re: Need query help No, that'll get one or the other - he wants ones that are both. Aliasing will work, something like this: James, I think I got this to work. My first tests are good. I didn't mention this is a highly dynamic query. I have quite a few other filters in play, but your example would work nicely with it. Thanks much! Will ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300417 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Need query help
What would that look like? Unioned result sets are not mutually dependant, but Will needs all his criteria to be met. ~Brad -Original Message- From: Adkins, Randy [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 04, 2008 10:00 AM To: CF-Talk Subject: RE: Need query help Could do a UNION to gather the results ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300419 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need query help
This should work and is much clearer that your original statement. SELECT t.groupid, t.groupimage, t.groupname, t.groupcity, t.state, t.voicingid, t.groupcontactperson, t.country, t.region, x.groupid, x.grouptype, gt.grouptype FROMtblgroups t, INNER JOIN tblgrouptypes gt ON gt.groupid = t.groupid, INNER JOIN tblgrouptypes_x x ON x.grouptype = gt.grouptype WHERE (gt.grouptype = 'College' AND gt.grouptype = 'Jewish') ORDER BY t.groupsortname ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300428 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Need query help
How could a value be equal to two things at once? Your where clause requires that the gt.grouptype column simultaneously equal 'College' and 'Jewish'. That query will return no results. ~Brad -Original Message- From: Nicholas Stein [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 04, 2008 10:50 AM To: CF-Talk Subject: Re: Need query help This should work and is much clearer that your original statement. SELECT t.groupid, t.groupimage, t.groupname, t.groupcity, t.state, t.voicingid, t.groupcontactperson, t.country, t.region, x.groupid, x.grouptype, gt.grouptype FROMtblgroups t, INNER JOIN tblgrouptypes gt ON gt.groupid = t.groupid, INNER JOIN tblgrouptypes_x x ON x.grouptype = gt.grouptype WHERE (gt.grouptype = 'College' AND gt.grouptype = 'Jewish') ORDER BY t.groupsortname ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300429 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need query help
Brad Wood wrote: If you query is highly dynamic, this way may not be best. Let's say the user can specify groups that are of type college, jewish, preschool, catholic, and skinny. Now, you have to join to the type table 5 times and that ain't looking too good. Joining is what databases are good at. Only I would rewrite the query a bit to only have one repeating part and keep it readable: cfset grouptypes = 'college,jewish,preschool,catholic,skinny' cfset i = 1 cfquery SELECT x.* FROM ( SELECT * FROM table WHERE table.y = cfqueryparam value=#ListFirst(grouptypes)# ) x cfloop list=#ListRest(grouptypes)# index=currentType cfset i++ INNER JOIN ( SELECT id FROM table WHERE table.y = cfqueryparam value=#currentType# ) y#i# ON x.id = y#i#.id /cfloop /cfquery Jochem ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300433 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Need query help
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 04, 2008 12:12 PM
To: CF-Talk
Subject: Re: Need query help
Joining is what databases are good at.
===
Flying is what birds are good at; but why send the whole flock when one
carrier pigeon will do? If there are hundreds of group types, why on
earth would you force your database to join the type table that many
times?
Will the solution work? Yes.
Is it simple and scalable? Not necessarily.
I have already suggested a solution (which no one seems that interested
in exploring) which would meet Will's requirements. It only requires a
single join to the type table and a group by regardless of how many
types there are.
Here is a working (and simplified) example which runs on MS SQL server.
declare @tblgroups table
( groupid int,
groupname varchar(50))
declare @tblgrouptypes table
( groupid int,
grouptype varchar(50))
insert into @tblgroups
(groupid, groupname)
select 1, 'group 1'
union select 2, 'group 2'
union select 3, 'group 3'
union select 4, 'group 4'
union select 5, 'group 5'
insert into @tblgrouptypes
(groupid, grouptype)
select 1, 'Jewish'
union select 1, 'Brave'
union select 2, 'Jewish'
union select 2, 'College'
union select 3, 'College'
union select 3, 'Young'
union select 3, 'Old'
union select 4, 'College'
union select 4, 'Young'
union select 5, 'Jewish'
union select 5, 'Old'
union select 5, 'College'
SELECT t.groupid,
t.groupname
FROM@tblgroups t
INNER JOIN @tblgrouptypes gt ON gt.groupid = t.groupid
WHERE gt.grouptype in ('College','Jewish') -- This would be dynamic
GROUP BY t.groupid,
t.groupname
HAVING count(*) = 2 -- This would be dynamic
ORDER BY t.groupname
~Brad
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300434
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need query help
Will Tomlinson wrote: Hey, I've got groups that can have one or more types associated with them. I have a linking table that I'm querying and filtering. But I'm not getting the results I need. Example: A group can be both 'College' and 'Jewish', as listed in the many linking table - tblgrouptypes_x.grouptype In my query below, I'm looking for groups that are both 'College' and 'Jewish', but getting no results. Even though I know there is one in there. Seems simple enough. SELECT tblgroups.groupid, tblgroups.groupimage, tblgroups.groupname, tblgroups.groupcity, tblgroups.state, tblgroups.voicingid, tblgroups.groupcontactperson, tblgroups.country, tblgroups.region, tblgrouptypes_x.groupid, tblgrouptypes_x.grouptype, tblgrouptypes.grouptype FROM tblgroups, tblgrouptypes, tblgrouptypes_x WHERE tblgroups.groupid = tblgrouptypes_x.groupid AND tblgrouptypes_x.grouptype = tblgrouptypes.grouptype AND (tblgrouptypes_x.grouptype = 'College' AND tblgrouptypes_x.grouptype = 'Jewish') ORDER BY tblgroups.groupsortname And ideas would be appreciated. Thanks, Will A problem with your query is that you SELECT the 'grouptype' column. This column cannot show both 'College' and 'Jewish' at the same time. One solution is to return two rows for the single record in tblgroups that you want. One row will have 'College' and the second row will have 'Jewish': - SELECT G.groupid, G.groupimage, G.groupname, G.groupcity, G.state, G.voicingid, G.groupcontactperson, G.country, G.region, X.groupid, X.grouptype, T.grouptype FROM tblgroups G, tblgrouptypes T, tblgrouptypes_x X WHERE G.groupid = X.groupid AND X.grouptype = T.grouptype AND EXISTS ( SELECT 1 FROM tblgrouptypes_x Z WHERE G.groupid = Z.groupid AND Z.grouptype = 'College' ) AND EXISTS ( SELECT 1 FROM tblgrouptypes_x Z WHERE G.groupid = Z.groupid AND Z.grouptype = 'Jewish' ) ORDER BY G.groupsortname - Another solution is to not SELECT the 'grouptype' column(s). This query will only return one row from tblgroups, containing only columns from tblgroups: - SELECT G.groupid, G.groupimage, G.groupname, G.groupcity, G.state, G.voicingid, G.groupcontactperson, G.country, G.region FROM tblgroups G WHERE G.groupid IN ( SELECT DISTINCT D.groupid FROM tblgroups D, tblgrouptypes_x X WHERE D.groupid = X.groupid AND EXISTS ( SELECT 1 FROM tblgrouptypes_x Z WHERE D.groupid = Z.groupid AND Z.grouptype = 'College' ) AND EXISTS ( SELECT 1 FROM tblgrouptypes_x Z WHERE D.groupid = Z.groupid AND Z.grouptype = 'Jewish' ) ) ORDER BY G.groupsortname - You don't mention your database. Both these SQL statements work on SQLServer and MySQL, but you may need to adjust them a bit if you use something else. Hope this helps. -tom- ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300477 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Need query help
Hey, I've got groups that can have one or more types associated with them. I have a linking table that I'm querying and filtering. But I'm not getting the results I need. Example: A group can be both 'College' and 'Jewish', as listed in the many linking table - tblgrouptypes_x.grouptype In my query below, I'm looking for groups that are both 'College' and 'Jewish', but getting no results. Even though I know there is one in there. Seems simple enough. SELECT tblgroups.groupid, tblgroups.groupimage, tblgroups.groupname, tblgroups.groupcity, tblgroups.state, tblgroups.voicingid, tblgroups.groupcontactperson, tblgroups.country, tblgroups.region, tblgrouptypes_x.groupid, tblgrouptypes_x.grouptype, tblgrouptypes.grouptype FROM tblgroups, tblgrouptypes, tblgrouptypes_x WHERE tblgroups.groupid = tblgrouptypes_x.groupid AND tblgrouptypes_x.grouptype = tblgrouptypes.grouptype AND (tblgrouptypes_x.grouptype = 'College' AND tblgrouptypes_x.grouptype = 'Jewish') ORDER BY tblgroups.groupsortname And ideas would be appreciated. Thanks, Will ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300344 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need query help
To make your query more readable, can you alias your tables? SELECT g.groupid, g.groupimage, g.groupname, g.groupcity, g.state, g.voicingid, g.groupcontactperson, g.country, g.region, gtx.groupid, gtx.grouptype, gt.grouptype FROM tblgroups g, tblgrouptypes gt, tblgrouptypes_x gtx WHERE g.groupid = gtx.groupid AND gtx.grouptype = gt.grouptype AND (gtx.grouptype = 'College' AND gtx.grouptype = 'Jewish') ORDER BY g.groupsortname If you take out one or the other, does it return the results? On Mon, Mar 3, 2008 at 4:39 PM, Will Tomlinson [EMAIL PROTECTED] wrote: Hey, I've got groups that can have one or more types associated with them. I have a linking table that I'm querying and filtering. But I'm not getting the results I need. Example: A group can be both 'College' and 'Jewish', as listed in the many linking table - tblgrouptypes_x.grouptype In my query below, I'm looking for groups that are both 'College' and 'Jewish', but getting no results. Even though I know there is one in there. Seems simple enough. SELECT tblgroups.groupid, tblgroups.groupimage, tblgroups.groupname, tblgroups.groupcity, tblgroups.state, tblgroups.voicingid, tblgroups.groupcontactperson, tblgroups.country, tblgroups.region, tblgrouptypes_x.groupid, tblgrouptypes_x.grouptype, tblgrouptypes.grouptype FROM tblgroups, tblgrouptypes, tblgrouptypes_x WHERE tblgroups.groupid = tblgrouptypes_x.groupid AND tblgrouptypes_x.grouptype = tblgrouptypes.grouptype AND (tblgrouptypes_x.grouptype = 'College' AND tblgrouptypes_x.grouptype = 'Jewish') ORDER BY tblgroups.groupsortname And ideas would be appreciated. Thanks, Will ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300348 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need query help
If you take out one or the other, does it return the results?
Yes. I get valid results if I remove one of the AND's. I can just pull jewish
groups for instance.
Originally, I used IN ('jewish',college').
But that returns ALL jewish groups, and ALL college groups. My client wants it
to return only groups that are both jewish AND college.
Thanks Greg,
Will
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300349
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Need query help
Try changing your AND statement to an OR statement: AND (tblgrouptypes_x.grouptype = 'College' OR tblgrouptypes_x.grouptype = 'Jewish') -Original Message- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2008 5:39 PM To: CF-Talk Subject: Need query help Hey, I've got groups that can have one or more types associated with them. I have a linking table that I'm querying and filtering. But I'm not getting the results I need. Example: A group can be both 'College' and 'Jewish', as listed in the many linking table - tblgrouptypes_x.grouptype In my query below, I'm looking for groups that are both 'College' and 'Jewish', but getting no results. Even though I know there is one in there. Seems simple enough. SELECT tblgroups.groupid, tblgroups.groupimage, tblgroups.groupname, tblgroups.groupcity, tblgroups.state, tblgroups.voicingid, tblgroups.groupcontactperson, tblgroups.country, tblgroups.region, tblgrouptypes_x.groupid, tblgrouptypes_x.grouptype, tblgrouptypes.grouptype FROM tblgroups, tblgrouptypes, tblgrouptypes_x WHERE tblgroups.groupid = tblgrouptypes_x.groupid AND tblgrouptypes_x.grouptype = tblgrouptypes.grouptype AND (tblgrouptypes_x.grouptype = 'College' AND tblgrouptypes_x.grouptype = 'Jewish') ORDER BY tblgroups.groupsortname And ideas would be appreciated. Thanks, Will ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300352 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Need query help
No, that won't return groups that are in the jewish AND college type.
I think you might need to join to the grouptypes table twice-- once for
each type you want.
I don't know your database schema, but you might be able to do grouptype
in ('jewish','college') then group by everything but group type and add
a having count(*) 1
I'm a little unsure of what your query is expected to return. If a
group can have multiple types, then I assume that there is a one-to-many
relationship between groups and types, where a single group record has
one or more type records.
You are returning type in your query, so a group with 5 type records,
would be duplicated 5 times in the result set-- once for each type. Do
you want this, or do you want a single record for the group no matter
how many types they match?
~Brad
-Original Message-
From: ColdFusion [mailto:[EMAIL PROTECTED]
Sent: Monday, March 03, 2008 5:37 PM
To: CF-Talk
Subject: RE: Need query help
Try changing your AND statement to an OR statement:
AND (tblgrouptypes_x.grouptype = 'College' OR tblgrouptypes_x.grouptype
=
'Jewish')
-Original Message-
From: Will Tomlinson [mailto:[EMAIL PROTECTED]
Sent: Monday, March 03, 2008 5:39 PM
To: CF-Talk
Subject: Need query help
Hey,
I've got groups that can have one or more types associated with them. I
have
a linking table that I'm querying and filtering. But I'm not getting the
results I need.
Example: A group can be both 'College' and 'Jewish', as listed in the
many
linking table - tblgrouptypes_x.grouptype
In my query below, I'm looking for groups that are both 'College' and
'Jewish', but getting no results. Even though I know there is one in
there.
Seems simple enough.
SELECT tblgroups.groupid, tblgroups.groupimage, tblgroups.groupname,
tblgroups.groupcity, tblgroups.state, tblgroups.voicingid,
tblgroups.groupcontactperson, tblgroups.country, tblgroups.region,
tblgrouptypes_x.groupid, tblgrouptypes_x.grouptype,
tblgrouptypes.grouptype
FROM tblgroups, tblgrouptypes, tblgrouptypes_x
WHERE tblgroups.groupid = tblgrouptypes_x.groupid
AND tblgrouptypes_x.grouptype = tblgrouptypes.grouptype
AND (tblgrouptypes_x.grouptype = 'College'
AND tblgrouptypes_x.grouptype = 'Jewish')
ORDER BY tblgroups.groupsortname
And ideas would be appreciated.
Thanks,
Will
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300355
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need query help
No, that'll get one or the other - he wants ones that are both. Aliasing will work, something like this: SELECT tblgroups.groupid, tblgroups.groupimage, tblgroups.groupname, tblgroups.groupcity, tblgroups.state, tblgroups.voicingid, tblgroups.groupcontactperson, tblgroups.country, tblgroups.region, x1.groupid as groupid1, x1.grouptype as grouptype1, x2.groupid as groupid2, x2.grouptype as grouptype2, gt1.grouptype, gt2.grouptype FROM tblgroups, tblgrouptypes gt1, tblgrouptypes gt2, tblgrouptypes_x x1, tblgrouptypes_x x2 WHERE tblgroups.groupid = x1.groupid AND x1.grouptype = tg1.grouptype, AND tblgroups.groupid = x2.groupid AND x2.grouptype = tg2.grouptype AND (x1.grouptype = 'College' AND x2.grouptype = 'Jewish') ORDER BY tblgroups.groupsortname On Tue, Mar 4, 2008 at 8:36 AM, ColdFusion [EMAIL PROTECTED] wrote: Try changing your AND statement to an OR statement: AND (tblgrouptypes_x.grouptype = 'College' OR tblgrouptypes_x.grouptype = 'Jewish') -Original Message- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2008 5:39 PM To: CF-Talk Subject: Need query help Hey, I've got groups that can have one or more types associated with them. I have a linking table that I'm querying and filtering. But I'm not getting the results I need. Example: A group can be both 'College' and 'Jewish', as listed in the many linking table - tblgrouptypes_x.grouptype In my query below, I'm looking for groups that are both 'College' and 'Jewish', but getting no results. Even though I know there is one in there. Seems simple enough. SELECT tblgroups.groupid, tblgroups.groupimage, tblgroups.groupname, tblgroups.groupcity, tblgroups.state, tblgroups.voicingid, tblgroups.groupcontactperson, tblgroups.country, tblgroups.region, tblgrouptypes_x.groupid, tblgrouptypes_x.grouptype, tblgrouptypes.grouptype FROM tblgroups, tblgrouptypes, tblgrouptypes_x WHERE tblgroups.groupid = tblgrouptypes_x.groupid AND tblgrouptypes_x.grouptype = tblgrouptypes.grouptype AND (tblgrouptypes_x.grouptype = 'College' AND tblgrouptypes_x.grouptype = 'Jewish') ORDER BY tblgroups.groupsortname And ideas would be appreciated. Thanks, Will ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300356 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need query help
I'm a little unsure of what your query is expected to return. If a group can have multiple types, then I assume that there is a one-to-many relationship between groups and types, where a single group record has one or more type records. Correct. You are returning type in your query, so a group with 5 type records, would be duplicated 5 times in the result set-- once for each type. Do you want this, or do you want a single record for the group no matter how many types they match? If there's a group that's jewish and college, I just need a single record returned. Thanks, Will ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300357 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Need query help
If there's a group that's jewish and college, I just need a single record returned. = Then removed type from the select list, group by everything else with an or equivalent in your where clause and add the having count(*) 1 to the end. That will only return groups which returned a record for both the Jewish and College type. Alternatively, inner joining to the type table twice, once for each type you want will work as well but might not perform as well. ~Brad ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300358 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need query help
If you HAVE to have both jewish and college types and only one record returned, try this: SELECT tblgroups.*, !--- i wouldn't do this, just saving space here ;) --- tblgrouptypes_x.groupid, tblgrouptypes_x.grouptype !--- perhaps returning this value is misleading as it can only tell you one of the group types --- FROM tblgroups INNER JOIN tblgrouptypes_x ON tblgrouptypes_x.groupid = tblgroups.groupid INNER JOIN tblgrouptypes ON tblgrouptypes.grouptype = tblgrouptypes_x.grouptype WHERE tblgrouptypes_x.grouptype = 'Jewish' AND EXISTS ( SELECT * FROM tblgrouptypes_x x2 INNER JOIN tblgrouptypes types2 ON types2.grouptype = x2.grouptype WHERE x2.groupId = tblgroups.groupid and types2.type = 'Colleges')) ORDER BY tblgroups.groupsortname Untested of course Dominic -- Blog it up: http://fusion.dominicwatson.co.uk ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300359 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need query help
No, that'll get one or the other - he wants ones that are both. Aliasing will work, something like this: James, I think I got this to work. My first tests are good. I didn't mention this is a highly dynamic query. I have quite a few other filters in play, but your example would work nicely with it. Thanks much! Will ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300361 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Help - find unique rows in two similar tables
It was pretty close. Running it in one direction, knowing that table B has extra records, works fine. - Original Message - From: Dawson, Michael [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Monday, February 11, 2008 7:59 PM Subject: RE: Query Help - find unique rows in two similar tables You know... This probably won't work. I don't have access to a database server right now to test this. I'm trying to picture the results, but it's late and my brain is foggy. M!ke -Original Message- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Monday, February 11, 2008 8:45 PM To: CF-Talk Subject: RE: Query Help - find unique rows in two similar tables SELECT a.* ,b.* FROM TableA a FULL OUTER JOIN TableB b ON a.id = b.id WHERE a.id IS NULL OR b.id IS NULL (Untested) M!ke ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298771 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query Help - find unique rows in two similar tables
SELECT a.* ,b.* FROM TableA a FULL OUTER JOIN TableB b ON a.id = b.id WHERE a.id IS NULL OR b.id IS NULL (Untested) M!ke -Original Message- From: Jim McAtee [mailto:[EMAIL PROTECTED] Sent: Monday, February 11, 2008 8:38 PM To: CF-Talk Subject: Query Help - find unique rows in two similar tables I have two tables with identical structures. The data in the two tables is _nearly_ identical, with one table having a few additional rows. I need to find those additional rows. I want to query the two tables and find the rows in one or the other that are not present in both. It would be OK to run two queries - show the rows in table A that aren't present in table B, then a second query to do the opposite. The primary keys in the two tables are _not_ the same, so rows must be deamed the same by examing a combination of three other columns. I've found some examples of queries to finding duplicate rows in two similar tables, but not the inverse. And most of those examples use the primary key. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298766 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query Help - find unique rows in two similar tables
You know... This probably won't work. I don't have access to a database server right now to test this. I'm trying to picture the results, but it's late and my brain is foggy. M!ke -Original Message- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Monday, February 11, 2008 8:45 PM To: CF-Talk Subject: RE: Query Help - find unique rows in two similar tables SELECT a.* ,b.* FROM TableA a FULL OUTER JOIN TableB b ON a.id = b.id WHERE a.id IS NULL OR b.id IS NULL (Untested) M!ke ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298768 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Query Help - find unique rows in two similar tables
I have two tables with identical structures. The data in the two tables is _nearly_ identical, with one table having a few additional rows. I need to find those additional rows. I want to query the two tables and find the rows in one or the other that are not present in both. It would be OK to run two queries - show the rows in table A that aren't present in table B, then a second query to do the opposite. The primary keys in the two tables are _not_ the same, so rows must be deamed the same by examing a combination of three other columns. I've found some examples of queries to finding duplicate rows in two similar tables, but not the inverse. And most of those examples use the primary key. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298764 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: query help
If ROUND isn't there, look for another rounding function like CEILING, FLOOR etc. Adrian -Original Message- From: Dave l Sent: 09 December 2007 04:18 To: CF-Talk Subject: Re: query help if i change the select statement to: SELECT center_id, address1, city, state, postalcode, state, ROUND((ACOS((SIN(#lat_A#/57.2958) * SIN(#lat_B#/57.2958)) + it changes the error to: 'ROUND' is not recognized as a function or procedure. so i might be sol on this tut ~| Get the answers you are looking for on the ColdFusion Labs Forum direct from active programmers and developers. http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294412 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: query help
http://www.ibm.com/developerworks/opensource/library/os-ad-trifecta6/ looks like you can use cast to round values On Dec 9, 2007 8:25 AM, Adrian Lynch [EMAIL PROTECTED] wrote: If ROUND isn't there, look for another rounding function like CEILING, FLOOR etc. Adrian -Original Message- From: Dave l Sent: 09 December 2007 04:18 To: CF-Talk Subject: Re: query help if i change the select statement to: SELECT center_id, address1, city, state, postalcode, state, ROUND((ACOS((SIN(#lat_A#/57.2958) * SIN(#lat_B#/57.2958)) + it changes the error to: 'ROUND' is not recognized as a function or procedure. so i might be sol on this tut ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294413 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: query help
Looks like CEIL() or CEILING() is the function you're looking for: http://db.apache.org/derby/docs/10.2/ref/refderby.pdf HTH, Jon On Dec 8, 2007, at 11:18 PM, Dave l wrote: if i change the select statement to: SELECTcenter_id, address1, city, state, postalcode, state, ROUND((ACOS((SIN(#lat_A#/57.2958) * SIN(#lat_B#/57.2958)) + it changes the error to: 'ROUND' is not recognized as a function or procedure. so i might be sol on this tut ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294414 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: query help
I'll take a look at those thanks guys Looks like CEIL() or CEILING() is the function you're looking for: http://db.apache.org/derby/docs/10.2/ref/refderby.pdf HTH, Jon On Dec 8, 2007, at 11:18 PM, Dave l wrote: if i change the select statement to: SELECT center_id, address1, city, state, postalcode, state, ROUND((ACOS((SIN(#lat_A#/57.2958) * SIN(#lat_B#/57.2958)) + it changes the error to: 'ROUND' is not recognized as a function or procedure. so i might be sol on this tut ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294431 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: query help
change one thing it errors on another lol now it errors on the , I'll take a look at those thanks guys ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294437 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
query help
i am trying to write a quite tutorial for learncf on a dealer locator. I guess you have to use one of the included db's so I am using one of the derby ones cause well.. thats all there is and this query is choking on it, it works on mysql but I know diddlysquat about derby the query SELECT *,ROUND((ACOS((SIN(#lat_A#/57.2958) * SIN(#lat_B#/57.2958)) + (COS(#lat_A#/57.2958) * COS(#lat_B#/57.2958) * COS(#lon_B#/57.2958 - #lon_A#/57.2958 * 3963) AS distance FROMcenters WHERE center_id = '23' AND (#lat_B# = #lat_A# - (#radius2#/111)) AND (#lat_B# = #lat_A# + (#radius2#/111)) AND (#lon_B# = #lon_A# - (#radius2#/111)) AND (#lon_B# = #lon_A# + (#radius2#/111)) - here is the error Error Executing Database Query. Syntax error: Encountered , at line 1, column 18. The error occurred in z:\Websites\ss\tutorials\zipcode\cfc\zipcode.cfc: line 43 41 :AND (#lat_B# = #lat_A# + (#radius2#/111)) 42 :AND (#lon_B# = #lon_A# - (#radius2#/111)) 43 :AND (#lon_B# = #lon_A# + (#radius2#/111)) 44 :/cfquery 45 : ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294409 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: query help
if i change the select statement to: SELECT center_id, address1, city, state, postalcode, state, ROUND((ACOS((SIN(#lat_A#/57.2958) * SIN(#lat_B#/57.2958)) + it changes the error to: 'ROUND' is not recognized as a function or procedure. so i might be sol on this tut ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294410 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
SOLVED: Query help...
I got it fixed. Thanks, Will ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294126 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Query help...
Hey, I have 3 tables - one to many rel. tblreports reportid PK lotsmorefieldsforfiltering tblquestioncomments questioncommentid PK reportid FK questionid FK tblquestions questionidPK Originally I just needed a question count based off a specific question ID. As my loop's output generated question ID's, I ran a query like this to get a count. SELECT COUNT(questionid) as numcomments FROM tblquestioncomments WHERE questionid = cfqueryparam cfsqltype=cf_sql_integer value=#questionid# Now I need to add some filters to it that filter off of tblreports criteria. I've got this but it doesn't work: SELECT tblreports.reportid, COUNT(*) as numcomments, tblquestioncomments.questionid FROM tblreports, tblquestioncomments WHERE tblreports.reportid = tblquestioncomments.reportid AND questionid = cfqueryparam cfsqltype=cf_sql_integer value=#questionid# !--- Filters goe here --- cfif FORM.tier7 NEQ All AND tblreports.orgtier7id = '#FORM.tier7#' /cfif cfif FORM.tier6 NEQ All AND tblreports.orgtier6id = '#FORM.tier6#' /cfif GROUP BY tblquestioncomments.questionid, tblreports.reportid How can I make this query give me a count of comments using the criteria provided? Thanks, Will ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294122 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Query help for Spry Dataset
I have three tables, worship_songs, worship_sets, worship_song_lists. worship_songs: worship_songID worship_song_title worship_song_lyrics worship_sets: worship_setID worship_set_title worship_set_date worship_song_lists: worship_song_listID worship_songID_fk worship_setID_fk I have a spry dataset that displays the current songs associated with a set list, as well as a dataset from which users can see a list of songs from which to composes a set list. Neither of these is difficult; however, I would like to be able to display an icon next to each song in the master list that indicates whether or not the song is in the currently selected set list. I am currently attempting the following: cfquery name=rsWorshipSongs datasource=#application.dsn# SELECT * FROM worship_songs JOIN worship_song_lists oN worship_songs.worship_songID = worship_song_lists.worship_songID_fk GROUP BY worship_songID /cfquery cfsavecontent variable=rsWorshipSongs ?xml version=1.0 encoding=UTF-8? cfoutput songs cfloop from=1 to=#rsWorshipSongs.recordCount# index=i song id#rsWorshipSongs.worship_songID[i]#/id title#rsWorshipSongs.worship_song_title[i]#/title cfif rsWorshipSongs.worship_setID_fk[i] EQ arguments.setID savedyes/saved cfelse savedno/saved /cfif /song /cfloop /songs /cfoutput /cfsavecontent The returned data in the saved attributes does not accurately reflect the songs which belong to the individual sets. If I take off the grouping, the records that do exist in the worship_song_lists are duplicated, while those that do not are not. I am seriously pulling my hair out on trying to get this query to return all the songs that exist while also showing their status in relation to the selected set list. Any help would be greatly appreciated! Thanks! Joel ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293562 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
