Re: OT : SQL List Comparison

2003-09-26 Thread Claude Schneegans
>>compare two lists in SQLIn SQL... or CFML?There are tags in the tag gallery for list union, intersection etc, but they cannot be used in queries of course. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

RE: OT : SQL List Comparison

2003-09-26 Thread DURETTE, STEVEN J (AIT)
What SQL server are you using? If it is MSSQL2000, I have created some list functions (I've mentioned themon cf-talk before).  One of them takes a list and returns a table with 2columns the item position in the list and the value of that item. If you are interested, I can send them to you.   The fu

RE: OT : SQL List Comparison

2003-09-26 Thread Robertson-Ravo, Neil (RX)
Managed it via this SQL : declare @List1 varchar(255), @List2 varchar(255), @sql varchar(255) set @List1 = '1,2,34,12,43,21,5,9,10'set @List2 = '34,12,43,21,23,35,19,5,10' create table #List1 (item  varchar(10))create table #List2 (item  varchar(10)) select @sql = 'insert #List1 select ' + replace(

Re: OT : SQL List Comparison

2003-09-26 Thread I-Lin Kuo
Depending on your database and exactly what you wantedto do with the list, You could try something likeupdate yourTable set selected=0where primKey in (select primKey from yourTable where primKey in(#List1#) MINUSselect primKey from yourTable where primKey in(#List2#))Clever combinations of the bas

RE: OT : SQL List Comparison

2003-09-26 Thread A.Little
If you need to track what's been inserted/deleted, then you could (beforeyou do any db inserts/deletes) loop through the old list and find whichvalues aren;t in the new list - to find whats been deleted, then loop thoughthe new list and find which values aren;t in the old list to find what'sbeen in

RE: OT : SQL List Comparison

2003-09-26 Thread Robertson-Ravo, Neil (RX)
Yep, I think I will have to do things in CF, though unfortunately forlogging purposes I need to know a little more about what they haveselected/removed before the deletion / insertion occurs.-Original Message-From: A.Little [mailto:[EMAIL PROTECTED]Sent: 26 September 2003 14:31To: CF-TalkSu

RE: OT : SQL List Comparison

2003-09-26 Thread A.Little
SQL doesn;t really cope with lists very well, there are some articles here(http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv ) about passingcsv strings to stored procs in SQL to then process, but as you'll see itstarts to get a little