Re: SQL query question
With ColdFusion 9, we ended up replacing all the wildcard selects with actual column names, among other fortifications. We did not experience this issue on 6.1. ~| 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:341821 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
SQL query question
Since everyone should be in a SQL Join state of mind...here's another one. I have a weird issue that I haven't been able to narrow down yet. I'm trying to add a new field to one of my tables to store some additional information, but once I add the column, my previously working query breaks. Here's the query (obviously the person that originally wrote it is in the 'the fewer characters, the better' camp): SELECT p.*, pt.*, pm.Type AS mtype, s.fname AS sfname, s.lname AS slname, pa.DateActive AS PenAmountDate, pa.MonthlyAmount AS PenAmt, pa.Note AS PenAmtNote, s.SSN AS sssn, s.DOB AS sdob, spm.Type AS smt FROM PensionerMedicalType spm INNER JOIN PensionerSpouse s ON spm.MedicalID = s.MedicalType RIGHT OUTER JOIN PensionersActive p INNER JOIN PensionerType pt ON p.PensionerType = pt.PensionerTID LEFT OUTER JOIN PensionAmounts pa ON p.PenActID = pa.PenID LEFT OUTER JOIN PensionerMedicalType pm ON p.MedicalType = pm.MedicalID ON s.SpouseID = p.Spouse WHERE (p.PenActID = cfqueryparam cfsqltype=cf_sql_integer value=#id# AND pa.active = 1) As soon as I add a column named 'lifeInsType' to the PensionersActive (p) table, I get the following error when the same query runs: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type. What am I overlooking? ~| 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:341801 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL query question
This might be an issue I've had to deal with before. Do something to change the fingerprint (no idea what the correct term would be) of the query - or restart cf. By change the fingerprint, it could be something as simple ad swapping p.* and pt.* (swapping as in their ordinal position in the select clause). In the past, what has happened is I'll have some query with at least one * in the select statement followed by one or more columns and I add a column to whatever I was selecting * from. If the query stays the same, it uses the same result-set template (again, im making up words - no idea what its really called) It doesn't know to check the underlying structure of the table. But the newly added column gets returned by the db - offsetting all the columns in the rest of the result set. So as crazy as it sounds - just change something in the sql, and give it a try. It doesn't have to actually change anything about what the query does - adding ,getdate() as helloworld will fix it - and then you can undo the change after one successful run. I assume this is something on CF's side - as restarting the CF service also fixes it. On Tue, Feb 1, 2011 at 4:23 PM, Debbie Morris deb_mor...@comcast.netwrote: Since everyone should be in a SQL Join state of mind...here's another one. I have a weird issue that I haven't been able to narrow down yet. I'm trying to add a new field to one of my tables to store some additional information, but once I add the column, my previously working query breaks. Here's the query (obviously the person that originally wrote it is in the 'the fewer characters, the better' camp): SELECT p.*, pt.*, pm.Type AS mtype, s.fname AS sfname, s.lname AS slname, pa.DateActive AS PenAmountDate, pa.MonthlyAmount AS PenAmt, pa.Note AS PenAmtNote, s.SSN AS sssn, s.DOB AS sdob, spm.Type AS smt FROM PensionerMedicalType spm INNER JOIN PensionerSpouse s ON spm.MedicalID = s.MedicalType RIGHT OUTER JOIN PensionersActive p INNER JOIN PensionerType pt ON p.PensionerType = pt.PensionerTID LEFT OUTER JOIN PensionAmounts pa ON p.PenActID = pa.PenID LEFT OUTER JOIN PensionerMedicalType pm ON p.MedicalType = pm.MedicalID ON s.SpouseID = p.Spouse WHERE (p.PenActID = cfqueryparam cfsqltype=cf_sql_integer value=#id# AND pa.active = 1) As soon as I add a column named 'lifeInsType' to the PensionersActive (p) table, I get the following error when the same query runs: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type. What am I overlooking? ~| 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:341803 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL query question
On 2/1/2011 1:23 PM, Debbie Morris wrote: What am I overlooking? The evil of using * in SELECT clauses. When that is done, database drivers are know to cache the columns and datatypes of the SQL queries. Then somebody comes along and changes the database structure, like you adding a field. Now the database structure does not match the cached structure stored by the database driver and this type of disconnect occurs. The solution: Replace those p.* and pt.* short cuts with that actual columns you need in your record set. ~| 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:341804 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL query question
On 2/1/2011 2:21 PM, Charlie Stell wrote: I assume this is something on CF's side - as restarting the CF service also fixes it. Not ColdFusion itself, but the database drivers used by ColdFusion and the cached (pooled) data source settings. Changing the Datasource to not used pooled settings might eliminate the caching of the database columns and types. But I have never tried it, having long ago accepted the better practice of not using * in my SQL. ~| 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:341805 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL query question
The evil of using * in SELECT clauses. I'm with Ian on this 100%. Often times developers think that using * will be faster, and easier and allow more flexibility. However that couldn't be further from the truth as you are seeing now. Take Ian's advice and define each column you want from your query. The added bonus is that there's no extra overhead associated with returning columns you aren't using. ~| 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:341806 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL query question
I'm 100% with you guys on this as well. Replacing the hundreds of select * from all the existing code here is one of my seemingly never ending tasks. I should have addressed that first before attempting to add anything else to the mix. I'm done installing my Windows updates for the evening, so I'll tackle this again in the morning. Thanks for the help! Debbie -Original Message- From: Michael Grant [mailto:mgr...@modus.bz] Sent: Tuesday, February 01, 2011 5:41 PM To: cf-talk Subject: Re: SQL query question The evil of using * in SELECT clauses. I'm with Ian on this 100%. Often times developers think that using * will be faster, and easier and allow more flexibility. However that couldn't be further from the truth as you are seeing now. Take Ian's advice and define each column you want from your query. The added bonus is that there's no extra overhead associated with returning columns you aren't using. ~| 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:341807 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Question
Ewok wrote: this is by far the easiest way to link tables. No... Its the only way to link tables that's why we do it. Not at all, you can link tables in many ways: you just have to do it all by hand instead of using the features that are provided by the database. It seems that you are becoming a little uptight about this I always get annoyed if people write something I don't understand. Especially if there is little explanation when asked. It is important to write proper English if you want people that learned English as a second language to understand what you are writing. We learned English from newspaper articles and books and that is the language we understand, not street language. Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207376 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL Query Question
S.Isaac Dealey wrote: I would expect in this case that having a primary key on the table he described wouldn't change the sql syntax much (if at all). It wouldn't change the syntax at all. For all we know it has a primary key and if not, how about the following one: ALTER TABLE ADD PRIMARY KEY (crID, actID) Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207377 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query Question
You seem to be going out of the way and way off the topic of the question to prove that it's okay to use your methods Jochem. No one said it wasn't okay. My point was that if he's having trouble with something that shouldnt be hard, then break it down to its simplest form and give every column its own purpose. Not at all, you can link tables in many ways: No, I'm pretty sure that relating columns is the only way to link tables to one another. I always get annoyed if people write something I don't understand. Especially if there is little explanation when asked. I'm sorry, but I dont know how else to explain it. The phrase, Easier that it already is, is hardly 'street language' and can't be easier to understand than it already is. :) Unless its a solution to the original question, which I've seen no one else offer yet, I say we drop this topic all together. Especially since Jeff hasn't bothered to give us more input. This thread is becoming more and more useless with every rebuttal and we are just adding more keywords to the archives that point to a pointless debate. cf_duckout -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Sunday, May 22, 2005 3:10 AM To: CF-Talk Subject: Re: SQL Query Question Ewok wrote: this is by far the easiest way to link tables. No... Its the only way to link tables that's why we do it. Not at all, you can link tables in many ways: you just have to do it all by hand instead of using the features that are provided by the database. It seems that you are becoming a little uptight about this I always get annoyed if people write something I don't understand. Especially if there is little explanation when asked. It is important to write proper English if you want people that learned English as a second language to understand what you are writing. We learned English from newspaper articles and books and that is the language we understand, not street language. Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207392 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query Question
My impression from his description was that the actID was a sequential number associated with the crID so in his case he would always know that there would be only one record with a given actID for any given crID which is a foreign key to another table -- and as he wanted all data on that row, select distinct and order by desc by themselves wouldn't have produced what he wanted, because when you select distinct on all columns every row is returned when there aren't any duplicates (as I suspect is the case here). He could have use cfoutput and grouped by the crID, though there is an easier way to return only the rows he needed. I do agree that in general it's good practice to include a primary key and a in many if not most cases created and modified dates. Use distinct and order descending by crid. In the future, I highly recommend a unique key (PK) or at least a date/time field to decide which are the latest records -Original Message- From: Jeff Chastain [mailto:[EMAIL PROTECTED] Sent: Friday, May 20, 2005 9:59 AM To: CF-Talk Subject: OT: SQL Query Question Sorry for the off-topic, but I have been beating on this one for a while and the only answer I can find is really, really ugly. So, hopefully somebody knows more about queries that I do and can show me the error of my ways I have a table that looks something like this crIDactIDdescriptionowner 1 1Test 444 1 2Test Update124 2 1Test 578 Now, what I need is a listing of all distinct crID records, where the actID is the greatest. Basically, this is a history setup. There are a series of change requests (crID) that each have 1 or more actions (actID). I need a snapshot of the most recent status of each change request ( max(actID) ). The problem I am having is with the aggregate functions and getting all of the rest of the fields at the same time. The following query returns the correct crID / actID combination, but how do I get the other fields? SELECT crID, MAX(actID) AS actID FROM test GROUP BY crID The only thing I have come up with thus far is having to run another query for each record returned in the above query in order to get the additional details and a query per row just can't be a good thing. The resulting data set that I am looking for would be as follows crIDactIDdescriptionowner 1 2Test Update124 2 1Test 578 Any pointers would be greatly appreciated and would save the few brain cells I have left. Thanks -- Jeff s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207343 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL Query Question
Ewok wrote: Use distinct and order descending by crid. In the future, I highly recommend a unique key (PK) or at least a date/time field to decide which are the latest records The datamodel is fine, crID and actID function nicely as composite primary key. Jochem ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207345 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query Question
How so with duplicates of both? -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Saturday, May 21, 2005 10:13 AM To: CF-Talk Subject: Re: SQL Query Question Ewok wrote: Use distinct and order descending by crid. In the future, I highly recommend a unique key (PK) or at least a date/time field to decide which are the latest records The datamodel is fine, crID and actID function nicely as composite primary key. Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207346 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query Question
I meant order descending by actid too, sorry. But maybe im misunderstanding your data structure since others see it a different way. Another... way group your cfouput... !--- THIS WILL CREATE THE SAMPLE DATA YOU USED --- cfset q = querynew(crID,actID,description,owner) cfset queryaddrow(q) cfset querysetcell(q, crid, 1) cfset querysetcell(q, actid, 1) cfset querysetcell(q, description, Test) cfset querysetcell(q, owner, 444) cfset queryaddrow(q) cfset querysetcell(q, crid, 1) cfset querysetcell(q, actid, 2) cfset querysetcell(q, description, Test Update) cfset querysetcell(q, owner, 124) cfset queryaddrow(q) cfset querysetcell(q, crid, 2) cfset querysetcell(q, actid, 1) cfset querysetcell(q, description, Test) cfset querysetcell(q, owner, 578) cfdump var=#q# !--- END SAMPLE DATA -- !--- get all the records in order by crid with their highest actid first --- cfquery name=rs dbtype=query select * from q order by crid, actid desc /cfquery !--- this will display exactly what was asked for --- !--- distinct crids with the highest actid of that crid --- pre crIDactID description owner cfoutput query=rs group=crid#crid##actID# #description# #owner# /cfoutput /pre -Original Message- From: Ewok [mailto:[EMAIL PROTECTED] Sent: Friday, May 20, 2005 10:40 PM To: CF-Talk Subject: RE: SQL Query Question Use distinct and order descending by crid. In the future, I highly recommend a unique key (PK) or at least a date/time field to decide which are the latest records -Original Message- From: Jeff Chastain [mailto:[EMAIL PROTECTED] Sent: Friday, May 20, 2005 9:59 AM To: CF-Talk Subject: OT: SQL Query Question Sorry for the off-topic, but I have been beating on this one for a while and the only answer I can find is really, really ugly. So, hopefully somebody knows more about queries that I do and can show me the error of my ways I have a table that looks something like this crIDactIDdescriptionowner 1 1Test 444 1 2Test Update124 2 1Test 578 Now, what I need is a listing of all distinct crID records, where the actID is the greatest. Basically, this is a history setup. There are a series of change requests (crID) that each have 1 or more actions (actID). I need a snapshot of the most recent status of each change request ( max(actID) ). The problem I am having is with the aggregate functions and getting all of the rest of the fields at the same time. The following query returns the correct crID / actID combination, but how do I get the other fields? SELECT crID, MAX(actID) AS actID FROM test GROUP BY crID The only thing I have come up with thus far is having to run another query for each record returned in the above query in order to get the additional details and a query per row just can't be a good thing. The resulting data set that I am looking for would be as follows crIDactIDdescriptionowner 1 2Test Update124 2 1Test 578 Any pointers would be greatly appreciated and would save the few brain cells I have left. Thanks -- Jeff ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207348 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL Query Question
Ewok wrote: How so with duplicates of both? Because combined they are a unique identifier. Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207354 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query Question
I guess thats one way of looking at it... I think creating the need to combine them is more work than its worth or would ever need to be. Valid or invalid, I still recommend something a little more easier to differentiate records with such as a single unique key per record. After all, what hes having trouble with would be less trouble than it' already not if they existed here. -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Saturday, May 21, 2005 2:41 PM To: CF-Talk Subject: Re: SQL Query Question Ewok wrote: How so with duplicates of both? Because combined they are a unique identifier. Jochem ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207356 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL Query Question
Ewok wrote: I guess that痴 one way of looking at it... I think creating the need to combine them is more work than its worth or would ever need to be. Valid or invalid, I still recommend something a little more easier to differentiate records with such as a single unique key per record. I think adding an extra field solely for a unique identifier only complicates the matter. Especially in many to many relations this is by far the easiest way to link tables. Though I suppose that if you really needed to you could always define a composite type. After all, what hes having trouble with would be less trouble than it' already not if they existed here. In English please. Jochem ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207358 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query Question
In English please. My sentiments exactly :) But... easier than it already is Anyway, you have your way and everyone else has theirs. If the way you are using is making it more difficult for you than it needs to be and can be simplified, then by all means simplify it. And I will most definitely have to disagree that adding a unique identifier to allow the table to stand on its own without the relations is hardly more complicated. this is by far the easiest way to link tables. No... Its the only way to link tables that's why we do it. But he didnt ask for data from multiple tables or how to link them, he asked for data from one table... the one without unique identifiers. It seems that you are becoming a little uptight about this (of course I could be wrong, it's hard to read tone) but please dont take any of this as an attack on your methods or a defense of my own. I've read a lot of your posts and solutions and have nothing but respect for you... I just respectfully disagree here. Nothing more. If your not getting uptight... I'll drop it here anyway because... well... pro-longed exposure to me tends to make people that way :) -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Saturday, May 21, 2005 6:27 PM To: CF-Talk Subject: Re: SQL Query Question Ewok wrote: I guess that痴 one way of looking at it... I think creating the need to combine them is more work than its worth or would ever need to be. Valid or invalid, I still recommend something a little more easier to differentiate records with such as a single unique key per record. I think adding an extra field solely for a unique identifier only complicates the matter. Especially in many to many relations this is by far the easiest way to link tables. Though I suppose that if you really needed to you could always define a composite type. After all, what hes having trouble with would be less trouble than it' already not if they existed here. In English please. Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207360 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query Question
If your not getting uptight... I'll drop it here anyway because... well... pro-longed exposure to me tends to make people that way :) Droids don't tear peoples arms out of their sockets if they... oh nevermind... :) s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207363 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query Question
After all, what hes having trouble with would be less trouble than it' already not if they existed here. I would expect in this case that having a primary key on the table he described wouldn't change the sql syntax much (if at all). I still like to have a primary key on these sorts of tables, but that's more of a preference at that point. Though I might have also designed the schema with two tables instead of one -- one table to contain the last entry and a separate table to contain all historical entries. It's hard to say without knowing more about the specifics of the app. s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207364 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query Question
True, more would be helpful. But a unique identifier would serve the purpose of deciding which was entered last which I believed he asked... but only he could clarify that and since he's not responding with any input... :) you calling Jochem a wookie? hehe -Original Message- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] Sent: Saturday, May 21, 2005 8:04 PM To: CF-Talk Subject: RE: SQL Query Question After all, what hes having trouble with would be less trouble than it' already not if they existed here. I would expect in this case that having a primary key on the table he described wouldn't change the sql syntax much (if at all). I still like to have a primary key on these sorts of tables, but that's more of a preference at that point. Though I might have also designed the schema with two tables instead of one -- one table to contain the last entry and a separate table to contain all historical entries. It's hard to say without knowing more about the specifics of the app. s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207365 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query Question
True, more would be helpful. But a unique identifier would serve the purpose of deciding which was entered last which I believed he asked... but only he could clarify that and since he's not responding with any input... :) you calling Jochem a wookie? hehe Actually it was a mangled reference to you saying that prolonged exposure to you upsets people... I propose a new strategy R-2... Let the Ewok win. Which is what the oh nevermind... at the end was about. :P s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207368 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
OT: SQL Query Question
Sorry for the off-topic, but I have been beating on this one for a while and the only answer I can find is really, really ugly. So, hopefully somebody knows more about queries that I do and can show me the error of my ways I have a table that looks something like this crIDactIDdescriptionowner 1 1Test 444 1 2Test Update124 2 1Test 578 Now, what I need is a listing of all distinct crID records, where the actID is the greatest. Basically, this is a history setup. There are a series of change requests (crID) that each have 1 or more actions (actID). I need a snapshot of the most recent status of each change request ( max(actID) ). The problem I am having is with the aggregate functions and getting all of the rest of the fields at the same time. The following query returns the correct crID / actID combination, but how do I get the other fields? SELECT crID, MAX(actID) AS actID FROM test GROUP BY crID The only thing I have come up with thus far is having to run another query for each record returned in the above query in order to get the additional details and a query per row just can't be a good thing. The resulting data set that I am looking for would be as follows crIDactIDdescriptionowner 1 2Test Update124 2 1Test 578 Any pointers would be greatly appreciated and would save the few brain cells I have left. Thanks -- Jeff ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207252 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Query Question
select t.* from test t where t.actid in (SELECT MAX(t2.actID) FROM test t2 where t2.crID = t.crID) ought to work... s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207259 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Query Question
Jeff Chastain wrote: I have a table that looks something like this crIDactIDdescriptionowner 1 1Test 444 1 2Test Update124 2 1Test 578 Now, what I need is a listing of all distinct crID records, where the actID is the greatest. Basically, this is a history setup. There are a series of change requests (crID) that each have 1 or more actions (actID). I need a snapshot of the most recent status of each change request ( max(actID) ). The problem I am having is with the aggregate functions and getting all of the rest of the fields at the same time. The following query returns the correct crID / actID combination, but how do I get the other fields? SELECT crID, MAX(actID) AS actID FROM test GROUP BY crID By joining this result to the original table: SELECT a.* FROMtest a NATURAL JOIN ( SELECT crID, MAX(actID) AS actID FROM test GROUP BY crID) b Jochem ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207269 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Query Question
I knew there was any easier way to do this. Thanks -- Jeff From: S. Isaac Dealey [EMAIL PROTECTED] Sent: Friday, May 20, 2005 9:16 AM To: CF-Talk cf-talk@houseoffusion.com Subject: Re: OT: SQL Query Question select t.* from test t where t.actid in (SELECT MAX(t2.actID) FROM test t2 where t2.crID = t.crID) ought to work... s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207275 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Query Question
Welcome... I'm sure Jochem's works well too -- assuming the db supports a natural join on an aliased sub-query ... I honestly had never seen that before, so I have no idea what the support for it is like with various db's. But it did peak my curiosity. :) I knew there was any easier way to do this. Thanks -- Jeff From: S. Isaac Dealey [EMAIL PROTECTED] Sent: Friday, May 20, 2005 9:16 AM To: CF-Talk cf-talk@houseoffusion.com Subject: Re: OT: SQL Query Question select t.* from test t where t.actid in (SELECT MAX(t2.actID) FROM test t2 where t2.crID = t.crID) ought to work... s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207279 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query Question
Use distinct and order descending by crid. In the future, I highly recommend a unique key (PK) or at least a date/time field to decide which are the latest records -Original Message- From: Jeff Chastain [mailto:[EMAIL PROTECTED] Sent: Friday, May 20, 2005 9:59 AM To: CF-Talk Subject: OT: SQL Query Question Sorry for the off-topic, but I have been beating on this one for a while and the only answer I can find is really, really ugly. So, hopefully somebody knows more about queries that I do and can show me the error of my ways I have a table that looks something like this crIDactIDdescriptionowner 1 1Test 444 1 2Test Update124 2 1Test 578 Now, what I need is a listing of all distinct crID records, where the actID is the greatest. Basically, this is a history setup. There are a series of change requests (crID) that each have 1 or more actions (actID). I need a snapshot of the most recent status of each change request ( max(actID) ). The problem I am having is with the aggregate functions and getting all of the rest of the fields at the same time. The following query returns the correct crID / actID combination, but how do I get the other fields? SELECT crID, MAX(actID) AS actID FROM test GROUP BY crID The only thing I have come up with thus far is having to run another query for each record returned in the above query in order to get the additional details and a query per row just can't be a good thing. The resulting data set that I am looking for would be as follows crIDactIDdescriptionowner 1 2Test Update124 2 1Test 578 Any pointers would be greatly appreciated and would save the few brain cells I have left. Thanks -- Jeff ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207328 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
SQL Query Question
We are using CF5.x and MSSQL server and I have a particular conundrum I am hoping one of you SQL experts can assist with. I have a record set like so: PN, V1, V2, V3...V15 Each V field contains a numeric value for each record. So a short record set might look something like: PN, V1, V2, V3 A 120 240 460 B 120 220 440 C 220 460 480 D 240 460 120 I need to query the database and come up with a result set of unique values for ALL the V fields across all records. So using the example above the optimum result would be a query with the result that would be: 120 220 240 440 460 480 I would like to let the database do the work instead of doing it in CF with loops but I am out of my depth on this particular query set. Any assistance would be appreciated. Best Regards, Dennis Powers UXB Internet- A Website Design and Hosting Company 690 Wolcott Road - P.O. Box 6028 Wolcott, CT 06716tel: (203)879-2844 http://www.uxbinternet.com http://dennis.uxb.net ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189653 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query Question
Others will probably come up with a better way, but my first thought was a series of unions. SELECT DISTINCT values FROM ( SELECT DISTINCT v1 AS values FROM table UNION SELECT DISTINCT v2 AS values FROM table UNION SELECT DISTINCT v3 AS values FROM table ) -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning ...-Original Message- ...From: Dennis Powers [mailto:[EMAIL PROTECTED] ...Sent: Friday, January 07, 2005 8:01 AM ...To: CF-Talk ...Subject: SQL Query Question ... ...We are using CF5.x and MSSQL server and I have a particular conundrum I ...am ...hoping one of you SQL experts can assist with. ... ...I have a record set like so: PN, V1, V2, V3...V15 Each V field ...contains a ...numeric value for each record. So a short record set might look ...something ...like: ... ...PN, V1, V2, V3 ...A 120 240 460 ...B 120 220 440 ...C 220 460 480 ...D 240 460 120 ... ...I need to query the database and come up with a result set of unique ...values ...for ALL the V fields across all records. So using the example above ...the ...optimum result would be a query with the result that would be: ... ...120 ...220 ...240 ...440 ...460 ...480 ... ...I would like to let the database do the work instead of doing it in CF ...with ...loops but I am out of my depth on this particular query set. Any ...assistance ...would be appreciated. ... ... ...Best Regards, ... ...Dennis Powers ...UXB Internet- A Website Design and Hosting Company ...690 Wolcott Road - P.O. Box 6028 ...Wolcott, CT 06716tel: (203)879-2844 ...http://www.uxbinternet.com ...http://dennis.uxb.net ... ... ... ... ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189655 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query Question
Ian, Thanks!!! That put me on the right track. I had never used a Union before and didn't even think about it. The resultant query looks like: SELECT DISTINCT v1 AS V_values FROM table UNION SELECT DISTINCT v2 AS V_values FROM table UNION SELECT DISTINCT v3 AS V_values FROM table ORDER BY V_values By default the Union operation eliminates duplicate rows as part of it's processing (from My SQL book) so it natively accomplishes what I was looking for in the first place. Best Regards, Dennis Powers UXB Internet- A Website Design and Hosting Company 690 Wolcott Road - P.O. Box 6028 Wolcott, CT 06716tel: (203)879-2844 http://www.uxbinternet.com http://dennis.uxb.net ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189686 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
SQL Query question. Please help...
Say I have two simple queries: cfquery name=getCustsFromCusts datasource=#DSN# SELECT CustID FROMCustomers /cfquery [and that query returns 700 records.] cfquery name=getCustsFromOrders datasource=#DSN# SELECT CustID FROMOrders /cfquery [and that query returns 500 records.] The first query gets all Customer IDs. The second query gets all Csutomer IDs that have placed orders. Is there some way in SQL, to combine these two queries into one and return only the Customer IDs that are in the 'Customers' table that ARE NOT in the 'Orders' table. Using this example, 200 records would be returned. Any help would be appreciated. Che ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185681 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL Query question. Please help...
Multiple ways heres one: SELECT CustID FROMCustomers WHERE CustID NOT IN (Select CustID From Orders) Adam On Tue, 30 Nov 2004 12:01:30 -0500, Che Vilnonis [EMAIL PROTECTED] wrote: Say I have two simple queries: cfquery name=getCustsFromCusts datasource=#DSN# SELECT CustID FROMCustomers /cfquery [and that query returns 700 records.] cfquery name=getCustsFromOrders datasource=#DSN# SELECT CustID FROMOrders /cfquery [and that query returns 500 records.] The first query gets all Customer IDs. The second query gets all Csutomer IDs that have placed orders. Is there some way in SQL, to combine these two queries into one and return only the Customer IDs that are in the 'Customers' table that ARE NOT in the 'Orders' table. Using this example, 200 records would be returned. Any help would be appreciated. Che ~| Special thanks to the CF Community Suite Silver Sponsor - CFDynamics http://www.cfdynamics.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185684 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query question. Please help...
How about this? Select CustID from customers where custID not in (select custid from orders) -dov -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 12:01 PM To: CF-Talk Subject: SQL Query question. Please help... Say I have two simple queries: cfquery name=getCustsFromCusts datasource=#DSN# SELECT CustID FROMCustomers /cfquery [and that query returns 700 records.] cfquery name=getCustsFromOrders datasource=#DSN# SELECT CustID FROMOrders /cfquery [and that query returns 500 records.] The first query gets all Customer IDs. The second query gets all Csutomer IDs that have placed orders. Is there some way in SQL, to combine these two queries into one and return only the Customer IDs that are in the 'Customers' table that ARE NOT in the 'Orders' table. Using this example, 200 records would be returned. Any help would be appreciated. Che ~| Special thanks to the CF Community Suite Silver Sponsor - CFDynamics http://www.cfdynamics.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185685 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query question. Please help...
Sometimes using NOT can have performance issues You can also do: SELECT a.CustID FROMCustomers a,Orders b WHERE a.CustID = b.CustID -Original Message- From: Adam Haskell [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 11:06 AM To: CF-Talk Subject: Re: SQL Query question. Please help... Multiple ways heres one: SELECT CustID FROMCustomers WHERE CustID NOT IN (Select CustID From Orders) Adam On Tue, 30 Nov 2004 12:01:30 -0500, Che Vilnonis [EMAIL PROTECTED] wrote: Say I have two simple queries: cfquery name=getCustsFromCusts datasource=#DSN# SELECT CustID FROMCustomers /cfquery [and that query returns 700 records.] cfquery name=getCustsFromOrders datasource=#DSN# SELECT CustID FROMOrders /cfquery [and that query returns 500 records.] The first query gets all Customer IDs. The second query gets all Csutomer IDs that have placed orders. Is there some way in SQL, to combine these two queries into one and return only the Customer IDs that are in the 'Customers' table that ARE NOT in the 'Orders' table. Using this example, 200 records would be returned. Any help would be appreciated. Che ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185686 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query question. Please help...
Lookingat this more you will probably only want unique customers so it some opne places multiple orders you may want to throw a distinct in there SELECT DISTINCT a.CustID FROMCustomers a,Orders b WHERE a.CustID = b.CustID -Original Message- From: Eric Creese Sent: Tuesday, November 30, 2004 11:05 AM To: CF-Talk Subject: RE: SQL Query question. Please help... Sometimes using NOT can have performance issues You can also do: SELECT a.CustID FROMCustomers a,Orders b WHERE a.CustID = b.CustID -Original Message- From: Adam Haskell [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 11:06 AM To: CF-Talk Subject: Re: SQL Query question. Please help... Multiple ways heres one: SELECT CustID FROMCustomers WHERE CustID NOT IN (Select CustID From Orders) Adam On Tue, 30 Nov 2004 12:01:30 -0500, Che Vilnonis [EMAIL PROTECTED] wrote: Say I have two simple queries: cfquery name=getCustsFromCusts datasource=#DSN# SELECT CustID FROMCustomers /cfquery [and that query returns 700 records.] cfquery name=getCustsFromOrders datasource=#DSN# SELECT CustID FROMOrders /cfquery [and that query returns 500 records.] The first query gets all Customer IDs. The second query gets all Csutomer IDs that have placed orders. Is there some way in SQL, to combine these two queries into one and return only the Customer IDs that are in the 'Customers' table that ARE NOT in the 'Orders' table. Using this example, 200 records would be returned. Any help would be appreciated. Che ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185687 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL Query question. Please help...
select custid from customers minus select custid from orders On Tue, 30 Nov 2004 11:05:11 -0600, Eric Creese [EMAIL PROTECTED] wrote: Sometimes using NOT can have performance issues You can also do: SELECT a.CustID FROMCustomers a,Orders b WHERE a.CustID = b.CustID -Original Message- From: Adam Haskell [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 11:06 AM To: CF-Talk Subject: Re: SQL Query question. Please help... Multiple ways heres one: SELECT CustID FROMCustomers WHERE CustID NOT IN (Select CustID From Orders) Adam On Tue, 30 Nov 2004 12:01:30 -0500, Che Vilnonis [EMAIL PROTECTED] wrote: Say I have two simple queries: cfquery name=getCustsFromCusts datasource=#DSN# SELECT CustID FROMCustomers /cfquery [and that query returns 700 records.] cfquery name=getCustsFromOrders datasource=#DSN# SELECT CustID FROMOrders /cfquery [and that query returns 500 records.] The first query gets all Customer IDs. The second query gets all Csutomer IDs that have placed orders. Is there some way in SQL, to combine these two queries into one and return only the Customer IDs that are in the 'Customers' table that ARE NOT in the 'Orders' table. Using this example, 200 records would be returned. Any help would be appreciated. Che ~| Special thanks to the CF Community Suite Silver Sponsor - CFDynamics http://www.cfdynamics.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185688 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL Query question. Please help...
Sometimes not join in the from clause can have performance issues too plus that will only return customers that are in both tables. If you prefer ther other route youi can do this: Select Distinct cust.CustId From Customers cust LEFT JOIN orders ON cust.custID = orders.CustId Where Orders.CustId IS NULL Either way 98% of the time if you look at the excecution plan that is produced by SQL server you will find them to be the same. Other DBMS I don't know about. Adam H On Tue, 30 Nov 2004 11:05:11 -0600, Eric Creese [EMAIL PROTECTED] wrote: Sometimes using NOT can have performance issues You can also do: SELECT a.CustID FROMCustomers a,Orders b WHERE a.CustID = b.CustID -Original Message- From: Adam Haskell [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 11:06 AM To: CF-Talk Subject: Re: SQL Query question. Please help... Multiple ways heres one: SELECT CustID FROMCustomers WHERE CustID NOT IN (Select CustID From Orders) Adam On Tue, 30 Nov 2004 12:01:30 -0500, Che Vilnonis [EMAIL PROTECTED] wrote: Say I have two simple queries: cfquery name=getCustsFromCusts datasource=#DSN# SELECT CustID FROMCustomers /cfquery [and that query returns 700 records.] cfquery name=getCustsFromOrders datasource=#DSN# SELECT CustID FROMOrders /cfquery [and that query returns 500 records.] The first query gets all Customer IDs. The second query gets all Csutomer IDs that have placed orders. Is there some way in SQL, to combine these two queries into one and return only the Customer IDs that are in the 'Customers' table that ARE NOT in the 'Orders' table. Using this example, 200 records would be returned. Any help would be appreciated. Che ~| Special thanks to the CF Community Suite Silver Sponsor - CFDynamics http://www.cfdynamics.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185689 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query question. Please help...
Adam/Dov Using your query my recordcount is off. Actual # of customer ids from my customers table = 865. Actual # of customer ids from my orders table = 596. Using your query, the difference = 335. 865 - 596 should = 269. What am I missing? Debug info is below. - getCustsFromCusts (Records=865, Time=0ms) SQL = SELECT CustID FROMCustomers getCustsFromOrders (Records=596, Time=15ms) SQL = SELECT CustID FROMOrders getCustsNoOrder (Records=335, Time=16ms) SQL = SELECT CustID FROMCustomers WHERE CustID NOT IN (Select CustID From Orders) ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185690 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL Query question. Please help...
Under your math you are assuming a 1 to 1 relationship between orders and Custromers. Unless you are running a scam, where no customer would ever by from you twice, that is not case, the whole reason you have 2 seperate tables. Hope thats makes sense. Adam H On Tue, 30 Nov 2004 12:18:23 -0500, Che Vilnonis [EMAIL PROTECTED] wrote: Adam/Dov Using your query my recordcount is off. Actual # of customer ids from my customers table = 865. Actual # of customer ids from my orders table = 596. Using your query, the difference = 335. 865 - 596 should = 269. What am I missing? Debug info is below. - getCustsFromCusts (Records=865, Time=0ms) SQL = SELECT CustID FROMCustomers getCustsFromOrders (Records=596, Time=15ms) SQL = SELECT CustID FROMOrders getCustsNoOrder (Records=335, Time=16ms) SQL = SELECT CustID FROMCustomers WHERE CustID NOT IN (Select CustID From Orders) ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185691 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query question. Please help...
You have multiple Orders for some customers. What is count for: SELECT DISTINCT Custid from Orders -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 12:18 PM To: CF-Talk Subject: RE: SQL Query question. Please help... Adam/Dov Using your query my recordcount is off. Actual # of customer ids from my customers table = 865. Actual # of customer ids from my orders table = 596. Using your query, the difference = 335. 865 - 596 should = 269. What am I missing? Debug info is below. - getCustsFromCusts (Records=865, Time=0ms) SQL = SELECT CustID FROMCustomers getCustsFromOrders (Records=596, Time=15ms) SQL = SELECT CustID FROMOrders getCustsNoOrder (Records=335, Time=16ms) SQL = SELECT CustID FROMCustomers WHERE CustID NOT IN (Select CustID From Orders) ~| Special thanks to the CF Community Suite Silver Sponsor - CFDynamics http://www.cfdynamics.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185693 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query question. Please help...
I see. Haven't had my lunch yet. Thanks for your help. Che -Original Message- From: Adam Haskell [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 12:23 PM To: CF-Talk Subject: Re: SQL Query question. Please help... Under your math you are assuming a 1 to 1 relationship between orders and Custromers. Unless you are running a scam, where no customer would ever by from you twice, that is not case, the whole reason you have 2 seperate tables. Hope thats makes sense. Adam H On Tue, 30 Nov 2004 12:18:23 -0500, Che Vilnonis [EMAIL PROTECTED] wrote: Adam/Dov Using your query my recordcount is off. Actual # of customer ids from my customers table = 865. Actual # of customer ids from my orders table = 596. Using your query, the difference = 335. 865 - 596 should = 269. What am I missing? Debug info is below. - getCustsFromCusts (Records=865, Time=0ms) SQL = SELECT CustID FROMCustomers getCustsFromOrders (Records=596, Time=15ms) SQL = SELECT CustID FROMOrders getCustsNoOrder (Records=335, Time=16ms) SQL = SELECT CustID FROMCustomers WHERE CustID NOT IN (Select CustID From Orders) ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185694 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL Query question. Please help... HA!
At 12:22 PM 11/30/2004, Adam Haskell wrote: Under your math you are assuming a 1 to 1 relationship between orders and Custromers. Unless you are running a scam, where no customer would ever by from you twice, that is not case, the whole reason you have 2 seperate tables. Hope thats makes sense. Ahhh, database design is always easier when there is a scam involved. And when programming for the mob, you never need to have a confirmation for the delete button. ;) Anders +===+ |Anders GreenEmail: [EMAIL PROTECTED]| | Home: 919.303.0218| | Off Road Rally Racing Team: http://LinaRacing.com/ | |Do you like Evite? This is better: http://RSVPtracker.com/ | +===+ ~| Special thanks to the CF Community Suite Silver Sponsor - CFDynamics http://www.cfdynamics.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185695 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Advanced SQL Query Question
Hello, I'm working on a project where records will be added to a single table, and each record will be assigned to one of six categories (using a category column). Is there a way I can do a single query to pull the most recent record from each of the six categories? Or is my only option to do queries of queries on my CF template? Thanks, Jonathan Jonathan Mauney Manager, Digital Media Properties / Web Application Developer 1110 WBT AM / 107.9 the LINK (WLNK-FM) / Jefferson-Pilot Radio Network Jefferson-Pilot Communications Co. One Julian Price Place Charlotte, North Carolina 28208 704.374.3862 [voice] 704.374.3884 [fax] [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] [email] ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Host with the leader in ColdFusion hosting. Voted #1 ColdFusion host by CF Developers. Offering shared and dedicated hosting options. www.cfxhosting.com/default.cfm?redirect=10481 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Advanced SQL Query Question
Jonathan, Try this out: ** Untested code!!! ** SELECT * FROM tblWhatever a INNER JOIN ( SELECT category,max(update_date) update_date FROM tblWhatever ) max_dates ON a.category = max_dates.category AND a.update_date = max_dates.update_date -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 3:06 PM To: CF-Talk Subject: Advanced SQL Query Question Hello, I'm working on a project where records will be added to a single table, and each record will be assigned to one of six categories (using a category column). Is there a way I can do a single query to pull the most recent record from each of the six categories? Or is my only option to do queries of queries on my CF template? Thanks, Jonathan Jonathan Mauney Manager, Digital Media Properties / Web Application Developer 1110 WBT AM / 107.9 the LINK (WLNK-FM) / Jefferson-Pilot Radio Network Jefferson-Pilot Communications Co. One Julian Price Place Charlotte, North Carolina 28208 704.374.3862 [voice] 704.374.3884 [fax] [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] [email] ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Advanced SQL Query Question
Hello, I'm working on a project where records will be added to a single table, and each record will be assigned to one of six categories (using a category column). Is there a way I can do a single query to pull the most recent record from each of the six categories? Or is my only option to do queries of queries on my CF template? Not sure of your actual table structure or database but you could possibly use the UNION operator for this. Assuming your database is MS SQL Server and there is some column in your table that you can use to determine the most recent (such as an ID or timestamp column): SELECT TOP 1 * FROM tbl WHERE category = 'A' ORDER BY ID DESC UNION SELECT TOP 1 * FROM tbl WHERE category = 'B' ORDER BY ID DESC ... and so on for all the categories. If you had six categories, this would return six rows, one per category, with the most recent entry for each category. HTH, Stephen Thanks, Jonathan Jonathan Mauney Manager, Digital Media Properties / Web Application Developer 1110 WBT AM / 107.9 the LINK (WLNK-FM) / Jefferson-Pilot Radio Network Jefferson-Pilot Communications Co. One Julian Price Place Charlotte, North Carolina 28208 704.374.3862 [voice] 704.374.3884 [fax] [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] [email] ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribefor umid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Host with the leader in ColdFusion hosting. Voted #1 ColdFusion host by CF Developers. Offering shared and dedicated hosting options. www.cfxhosting.com/default.cfm?redirect=10481 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=2137. 2057.4 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Advanced SQL Query Question
This seemingly simple query can turn nasty in a hurry... If you don't have very many records then use CF to do it like so: SELECT FROM table1 ORDER BY theCategory, theDate cfoutput query=myquery group=theCategory #whatever# /cfoutput Notice this will only print the first record for each new category and since they are sorted by category then date you'll get the most recent. Your other option (if you have lots of records) is to pre-mark the most recent one with a boolean flag when you insert it. Your table would have a column named mostRecent that is either 0 or 1 and each time you insert a record you set all other records in that category to 0 and set the new one to 1. Then your query just says: SELECT ... FROM table1 WHERE mostRecent = 1 +---+ Bryan Love Database Analyst Macromedia Certified Professional Internet Application Developer TeleCommunication Systems [EMAIL PROTECTED] +---+ ...'If there must be trouble, let it be in my day, that my child may have peace'... - Thomas Paine, The American Crisis Let's Roll - Todd Beamer, Flight 93 -Original Message- From: Marlon Moyer [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 1:45 PM To: CF-Talk Subject: RE: Advanced SQL Query Question Jonathan, Try this out: ** Untested code!!! ** SELECT * FROM tblWhatever a INNER JOIN ( SELECT category,max(update_date) update_date FROM tblWhatever ) max_dates ON a.category = max_dates.category AND a.update_date = max_dates.update_date -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 3:06 PM To: CF-Talk Subject: Advanced SQL Query Question Hello, I'm working on a project where records will be added to a single table, and each record will be assigned to one of six categories (using a category column). Is there a way I can do a single query to pull the most recent record from each of the six categories? Or is my only option to do queries of queries on my CF template? Thanks, Jonathan Jonathan Mauney Manager, Digital Media Properties / Web Application Developer 1110 WBT AM / 107.9 the LINK (WLNK-FM) / Jefferson-Pilot Radio Network Jefferson-Pilot Communications Co. One Julian Price Place Charlotte, North Carolina 28208 704.374.3862 [voice] 704.374.3884 [fax] [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] [email] ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SQL query question
Hi everyone, Okay, I've been struggling with this one for a while, and I'm sure there's a solution, but I'm just not seeing it. I've got a collection of tables, among which are document, and attrib_xref. In the attrib_xref table, there's (among others) the following columns: DocID AttribID AttribValue Each document can have multiple attributes, so there may be many lines in this table, with the same DocID, but different AttribID/AttribValue pairs. What I'd like to be able to do is an and search for multiple attributes, so I'd like to be able to say that I'm looking for the document with DocID=x, where AttribID=12 and it's corresponding value is 'some text', AND where there's also a second Attrib_XRef record with DocID=x, where AttribID=24 and it's corresponding value is 'some other text' I also need this to be extensible - ie, a person may specify one attribute pair, or 2, or 3, or... etc. Any suggestions (short of re-designing the DB - that's out of my control, unfortunately). If someone can point me in the right direction, I'd really appreciate it... Thanks, Dirk __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query question
All I can quickly come up with would be to use self-joins for every attribute/value pair. select A.DocId from attrib_xref A inner join attrib_xref B on A.DocID = B.DocID where A.attribId = 12 and B.attribId = 24 and A.attribValue = 'some text' and B.attribValue = 'some other text' ... You'll have to dynamically write out the sql, adding another self join for every pair you want to search for. It should work, but it's gonna be dog slow I would think. Hopefully someone else can find a better solution for you... /rob -Original Message- From: Dirk Sieber [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 29, 2002 2:02 PM To: CF-Talk Subject: SQL query question Hi everyone, Okay, I've been struggling with this one for a while, and I'm sure there's a solution, but I'm just not seeing it. I've got a collection of tables, among which are document, and attrib_xref. In the attrib_xref table, there's (among others) the following columns: DocID AttribID AttribValue Each document can have multiple attributes, so there may be many lines in this table, with the same DocID, but different AttribID/AttribValue pairs. What I'd like to be able to do is an and search for multiple attributes, so I'd like to be able to say that I'm looking for the document with DocID=x, where AttribID=12 and it's corresponding value is 'some text', AND where there's also a second Attrib_XRef record with DocID=x, where AttribID=24 and it's corresponding value is 'some other text' I also need this to be extensible - ie, a person may specify one attribute pair, or 2, or 3, or... etc. Any suggestions (short of re-designing the DB - that's out of my control, unfortunately). If someone can point me in the right direction, I'd really appreciate it... Thanks, Dirk __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query question
perhaps something like this
I'm sure there is a better way but this may be a start.
HTH
Select AttribValue
From MyTable
Where 0 = 0
cfif isdefined('attributes.AttribID') and isdefined('attributes.DocID')
And (
cfset lc = 0
cfloop list=#attributes.AttribID# index='x'
cfloop list=#attributes.DocID# index='y'
cfset lc = #lc#+1
cfif lc eq 1
(MyTable.AttribID = #x# and MyTable.DocID = #y#)
cfelse
OR (dbo.personnel_appointment.department_id = #x#)
/cfif
/cfloop
/cfloop)
/cfif
David DiPietro
Systems Developer / Engineer
OSU College of Medicine Public Health
Voice (614) 292-5960
Fax (614) 292-0745
-Original Message-
From: Dirk Sieber [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 29, 2002 2:02 PM
To: CF-Talk
Subject: SQL query question
Hi everyone,
Okay, I've been struggling with this one for a while, and I'm sure there's a
solution, but I'm just not seeing it.
I've got a collection of tables, among which are document, and attrib_xref.
In the attrib_xref table, there's (among others) the following columns:
DocID
AttribID
AttribValue
Each document can have multiple attributes, so there may be many lines in
this table, with the same DocID, but different AttribID/AttribValue pairs.
What I'd like to be able to do is an and search for multiple attributes,
so I'd like to be able to say that I'm looking for the document with
DocID=x, where AttribID=12 and it's corresponding value is 'some text', AND
where there's also a second Attrib_XRef record with DocID=x, where
AttribID=24 and it's corresponding value is 'some other text'
I also need this to be extensible - ie, a person may specify one attribute
pair, or 2, or 3, or... etc.
Any suggestions (short of re-designing the DB - that's out of my control,
unfortunately).
If someone can point me in the right direction, I'd really appreciate it...
Thanks,
Dirk
__
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query question
let's try again
Select DocID,AttribID,AttribValue
From attrib_xref
Where 0 = 0
cfif isdefined('attributes.AttribID') and isdefined('attributes.DocID')
And (
cfset xc = 0
cfset yc = 0
cfloop list=#attributes.DocID# index='x'
cfset yc = 0
cfset xc = #xc#+1
cfif xc eq 1
(
cfloop list=#attributes.AttribID# index='y'
cfset yc = #yc#+1
cfif yc eq 1
(attrib_xref.DocID = #x# and attrib_xref.AttribID = #y#)
cfelse
OR (attrib_xref.DocID = #x# and attrib_xref.AttribID = #y#)
/cfif
/cfloop
)
cfelse
AND
(
cfloop list=#attributes.AttribID# index='y'
cfset yc = #yc#+1
cfif yc eq 1
(attrib_xref.DocID = #x# and attrib_xref.AttribID = #y#)
cfelse
OR (attrib_xref.DocID = #x# and attrib_xref.AttribID = #y#)
/cfif
/cfloop
)
/cfif
/cfloop)
/cfif
Order by attrib_xref.DocID
Now that's a Kludge!
David DiPietro
Systems Developer / Engineer
OSU College of Medicine Public Health
Voice (614) 292-5960
Fax (614) 292-0745
-Original Message-
From: Dirk Sieber [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 29, 2002 2:02 PM
To: CF-Talk
Subject: SQL query question
Hi everyone,
Okay, I've been struggling with this one for a while, and I'm sure there's a
solution, but I'm just not seeing it.
I've got a collection of tables, among which are document, and attrib_xref.
In the attrib_xref table, there's (among others) the following columns:
DocID
AttribID
AttribValue
Each document can have multiple attributes, so there may be many lines in
this table, with the same DocID, but different AttribID/AttribValue pairs.
What I'd like to be able to do is an and search for multiple attributes,
so I'd like to be able to say that I'm looking for the document with
DocID=x, where AttribID=12 and it's corresponding value is 'some text', AND
where there's also a second Attrib_XRef record with DocID=x, where
AttribID=24 and it's corresponding value is 'some other text'
I also need this to be extensible - ie, a person may specify one attribute
pair, or 2, or 3, or... etc.
Any suggestions (short of re-designing the DB - that's out of my control,
unfortunately).
If someone can point me in the right direction, I'd really appreciate it...
Thanks,
Dirk
__
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query question
So, this is where I start thinking that maybe there isn't that elusive easy solution after all... ;-) Thanks for your suggestions... I'm going to start playing around with them now, and see what I can come up with. Dirk __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
SQL Query Question
I'm having trouble with this query. There are two tables, issue and issueOption. There is a one-to-many relationship between issue (one) and issueOption (many). issID is the key that joins the two tables. I want to select all records from issue that are not in the issueOption. In other words, get all issues without options. Thanks in advance, Nick __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Question
Something like this might work: SELECT DISTINCT IssID FROM Issue WHERE NOT EXISTS (SELECT * FROM issueOption WHERE issueOption.issID = Issue.issID) -Original Message- From: Nick Bourgeois [mailto:[EMAIL PROTECTED]] Sent: Sunday, May 19, 2002 11:32 AM To: CF-Talk Subject: SQL Query Question I'm having trouble with this query. There are two tables, issue and issueOption. There is a one-to-many relationship between issue (one) and issueOption (many). issID is the key that joins the two tables. I want to select all records from issue that are not in the issueOption. In other words, get all issues without options. Thanks in advance, Nick __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Question
I'm having trouble with this query. There are two tables, issue and issueOption. There is a one-to-many relationship between issue (one) and issueOption (many). issID is the key that joins the two tables. I want to select all records from issue that are not in the issueOption. In other words, get all issues without options. Or how about Select issue.issID From issue left outer join issueOption on issue.issID=issueOption.issID Where issueOption.issID is NULL Not sure which is faster, the IN or LEFT OUTER JOIN Philip Arnold Technical Director Certified ColdFusion Developer ASP Multimedia Limited Switchboard: +44 (0)20 8680 8099 Fax: +44 (0)20 8686 7911 www.aspmedia.co.uk www.aspevents.net An ISO9001 registered company. ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
An embarrassing SQL query question
I've done a lot in CF but this silly little problem just stumped me at the moment. If anyone would be kind enough to straighten me out on this I'd much appreciate it! I have a database of products. I need to submit a value ( a pricerange) to the query that will return only the items in that range. I have cfparam name="pricerange" default="1 50" which will be changed depending on what value is submitted by the user, (eg. 51-100). The problem is that SELECT ID, ProductName, Price, Photo FROM Products WHERE (Price 50) works, but SELECT ID, ProductName, Price, Photo FROM Products WHERE (Price = #pricerange#) returns no records at all. Thank you for any help! britta ~~ Structure your ColdFusion code with Fusebox. Get the official book at https://secure.houseoffusion.com Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: An embarrassing SQL query question
Britta, You'll have to do some processing on PriceRange to turn it into: (value x) AND (value y), or into: value BETWEEN x and y You can't just plug "1 50" into it. What does "1 50" mean, anyway?? ;-) Lee (Bjork) Borkman http://bjork.net ColdFusion Tags by Bjork -Original Message- From: Britta Wingenroth (West Coast Web) I have a database of products. I need to submit a value ( a pricerange) to the query that will return only the items in that range. I have cfparam name="pricerange" default="1 50" which will be changed depending on what value is submitted by the user, (eg. 51-100). The problem is that SELECT ID, ProductName, Price, Photo FROM Products WHERE (Price 50) works, but SELECT ID, ProductName, Price, Photo FROM Products WHERE (Price = #pricerange#) returns no records at all. IMPORTANT NOTICE: This e-mail and any attachment to it is intended only to be read or used by the named addressee. It is confidential and may contain legally privileged information. No confidentiality or privilege is waived or lost by any mistaken transmission to you. If you receive this e-mail in error, please immediately delete it from your system and notify the sender. You must not disclose, copy or use any part of this e-mail if you are not the intended recipient. The RTA is not responsible for any unauthorised alterations to this e-mail or attachment to it. ~~ Structure your ColdFusion code with Fusebox. Get the official book at https://secure.houseoffusion.com Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
SQL Query Question
I have the following SP: CREATE PROCEDURE [n_speaker_list] AS SELECT DISTINCT s.f_name, s.l_name, s.pro_title, c.co_name, sm.subject, a.agenda_title, s.sp_file FROM speakers s, company c, subject_matter sm, agenda a WHERE a.time_slot = s.time_slot AND s.subject_id = sm.subject_id AND s.company_id = c.company_id AND s.sp_file is not null ORDER BY s.f_name, s.l_name I want to pull all records where the s.f_name and s.l_name are distinct but not the rest of the string. Can someone please enlighten me on how to do this? Thanks, Tim Bahlke Tim Bahlke Access your e-mail anywhere, at any time. Get your FREE BellSouth Web Mail account today! http://webmail.bellsouth.net Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
