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
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
> > 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
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
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
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 wrote: > > 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 = > 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
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. -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
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
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
> 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
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
> 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
> 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
> 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
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
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: > 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 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... select * from q order by crid, actid desc crIDactID description owner #crid##actID# #description# #owner# -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
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
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
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
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
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
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. 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
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...
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...
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...
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...
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: > > > > > > SELECT CustID > > FROMCustomers > > > > > > [and that query returns 700 records.] > > > > > > SELECT CustID > > FROMOrders > > > > > > [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...
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: > > > > > > SELECT CustID > > FROMCustomers > > > > > > [and that query returns 700 records.] > > > > > > SELECT CustID > > FROMOrders > > > > > > [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...
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: > > > SELECT CustID > FROMCustomers > > > [and that query returns 700 records.] > > > SELECT CustID > FROMOrders > > > [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...
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: > > > SELECT CustID > FROMCustomers > > > [and that query returns 700 records.] > > > SELECT CustID > FROMOrders > > > [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...
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: SELECT CustID FROMCustomers [and that query returns 700 records.] SELECT CustID FROMOrders [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...
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: > > > SELECT CustID > FROMCustomers > > > [and that query returns 700 records.] > > > SELECT CustID > FROMOrders > > > [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
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
RE: SQL query question
let's try again Select DocID,AttribID,AttribValue >From attrib_xref Where 0 = 0 And ( ( (attrib_xref.DocID = #x# and attrib_xref.AttribID = #y#) OR (attrib_xref.DocID = #x# and attrib_xref.AttribID = #y#) ) AND ( (attrib_xref.DocID = #x# and attrib_xref.AttribID = #y#) OR (attrib_xref.DocID = #x# and attrib_xref.AttribID = #y#) ) ) 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
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 And ( (MyTable.AttribID = #x# and MyTable.DocID = #y#) OR (dbo.personnel_appointment.department_id = #x#) ) 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
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... -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
> 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
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