Re: OT : SQL List Comparison

[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

[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 functions I created are:    1) listAppend    2) listDeleteAt    3) listFirst    4) listGetAt    5) listInsertAt    6) listLast    7) listLen    8) listPrepend    9) listSetAt    10) listToTable I would post them to the internet, but I don't have an ftp server at themoment.  I will work on that this weekend. Steve  -Original Message-From: Robertson-Ravo, Neil (RX)[mailto:[EMAIL PROTECTED]Sent: Friday, September 26, 2003 10:06 AMTo: CF-TalkSubject: RE: OT : SQL List ComparisonManaged 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(@List1,',', ' union select')exec (@sql)select @sql = 'insert #List2 select ' + replace(@List2,',', ' union select')exec (@sql)select 'Removed', * from #List1 where item not in (select item from #List2)select 'Added', * from #List2 where item not in (select item from #List1)drop table #List1drop table #List2-Original Message-From: A.Little [mailto:[EMAIL PROTECTED]Sent: 26 September 2003 14:49To: CF-TalkSubject: RE: OT : SQL List ComparisonIf 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 inserted. Then at least you'll have the history - still need to be CFthough!Alex-Original Message-From: Robertson-Ravo, Neil (RX)[mailto:[EMAIL PROTECTED] Sent: 26 September 2003 14:40To: CF-TalkSubject: RE: OT : SQL List ComparisonYep, 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.  _  [ Todays Threads   _  [ Todays Threads 
 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

RE: OT : SQL List Comparison

[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(@List1,',', ' union select')exec (@sql) select @sql = 'insert #List2 select ' + replace(@List2,',', ' union select')exec (@sql) select 'Removed', * from #List1 where item not in (select item from #List2)select 'Added', * from #List2 where item not in (select item from #List1) drop table #List1drop table #List2  -Original Message-From: A.Little [mailto:[EMAIL PROTECTED]Sent: 26 September 2003 14:49To: CF-TalkSubject: RE: OT : SQL List ComparisonIf 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 inserted. Then at least you'll have the history - still need to be CFthough!Alex-Original Message-From: Robertson-Ravo, Neil (RX)[mailto:[EMAIL PROTECTED] Sent: 26 September 2003 14:40To: CF-TalkSubject: RE: OT : SQL List ComparisonYep, 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.  _  [ Todays Threads 
 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

Re: OT : SQL List Comparison

[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 basic set operations usingUNION, MINUS, and NOT IN will perform set operationson lists.--- "Robertson-Ravo, Neil (RX)"<[EMAIL PROTECTED]> wrote:> Anyone know if and how you can compare two lists in> SQL?> > real world examples could be> > List 1 : 1,54,36,98,32,67> List 2 : 54,98,67> > Which obviously means 1,36,32 have been removed> > and indeed this could also be possible : > > List 1 : 1,54,36,98,32,67> List 2 : 54,98,67,36,100,12> > Which obvioulsy means that 1 & 32 have been removed> and 100 & 12 have been> added> > its a puzzler...it must be able to be be> done...?>
 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

RE: OT : SQL List Comparison

[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 inserted. Then at least you'll have the history - still need to be CFthough!Alex-Original Message-From: Robertson-Ravo, Neil (RX)[mailto:[EMAIL PROTECTED] Sent: 26 September 2003 14:40To: CF-TalkSubject: RE: OT : SQL List ComparisonYep, 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. 
 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

RE: OT : SQL List Comparison

[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-TalkSubject: RE: OT : SQL List ComparisonSQL doesn;t really cope with lists very well, there are some articles here( http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv < ">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 messy.I guess you're processing the results from a load of checkboxes and tryingto remove and add the ones the user has unticked/ticked. I would stick tousing CF to do the list processing - I'm sure someone will correct me if I;mway off! Just do one delete statement to remove all the existing references (for theparticular user or whatever), then loop through the list in CF and add allthe references from the new list.HTHAlex-Original Message-From: Robertson-Ravo, Neil (RX)[mailto:[EMAIL PROTECTED] Sent: 26 September 2003 14:14To: CF-TalkSubject: OT : SQL List ComparisonAnyone know if and how you can compare two lists in SQL?real world examples could beList 1 : 1,54,36,98,32,67List 2 : 54,98,67Which obviously means 1,36,32 have been removedand indeed this could also be possible : List 1 : 1,54,36,98,32,67List 2 : 54,98,67,36,100,12Which obvioulsy means that 1 & 32 have been removed and 100 & 12 have beenaddedits a puzzler...it must be able to be be done...?   _  [Todays _  [ Todays  
 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

RE: OT : SQL List Comparison

[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 messy.I guess you're processing the results from a load of checkboxes and tryingto remove and add the ones the user has unticked/ticked. I would stick tousing CF to do the list processing - I'm sure someone will correct me if I;mway off! Just do one delete statement to remove all the existing references (for theparticular user or whatever), then loop through the list in CF and add allthe references from the new list.HTHAlex-Original Message-From: Robertson-Ravo, Neil (RX)[mailto:[EMAIL PROTECTED] Sent: 26 September 2003 14:14To: CF-TalkSubject: OT : SQL List ComparisonAnyone know if and how you can compare two lists in SQL?real world examples could beList 1 : 1,54,36,98,32,67List 2 : 54,98,67Which obviously means 1,36,32 have been removedand indeed this could also be possible : List 1 : 1,54,36,98,32,67List 2 : 54,98,67,36,100,12Which obvioulsy means that 1 & 32 have been removed and 100 & 12 have beenaddedits a puzzler...it must be able to be be done...?   _  [Todays  
 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

OT : SQL List Comparison

[Robertson-Ravo, Neil (RX)]

Anyone know if and how you can compare two lists in SQL?real world examples could beList 1 : 1,54,36,98,32,67List 2 : 54,98,67Which obviously means 1,36,32 have been removedand indeed this could also be possible : List 1 : 1,54,36,98,32,67List 2 : 54,98,67,36,100,12Which obvioulsy means that 1 & 32 have been removed and 100 & 12 have beenaddedits a puzzler...it must be able to be be done...?
 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]