Solved

Comparing Two list with coldfusion

Posted on 2011-02-14
7
536 Views
Last Modified: 2013-12-24
I have two list that I'm adding to two different tables in a database which is working fine. Now I need to compare the list to see what words on on the both list. How do I loop over tableA to see what is contained in that list that is also in tableB?

0
Comment
Question by:timothylwatson
  • 4
  • 2
7 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 34891892
If the values are stored in a db table, you shouldn't need to loop.  Not unless you're doing something crazy like literally storing a delimited list of values in 1 column ...  Can you give an example of the values in tableA and B?
0
 

Author Comment

by:timothylwatson
ID: 34892029
The tables are set up the same id, dateAddedID from another table that generates the date and gives it an id, and the listword column both the tables use the same fields.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34892099
Yeah, but that doesn't the answer the question: what's an example of the values? What you're storing and how will determine the correct answer

ie Is there one record per word:

TableA
ID, Word
1, "rrr"
2, "xxxx"

TableB
ID, Word
1, "rrr"
2, "ccc"
3, "aaa"

... OR are you storing an actual list in each column

TableA
ID, Word
1, "rrr,xxx"

TableB
ID, Word
1, "rrr,ccc,aaaa"


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:timothylwatson
ID: 34892186
Sorry.

Yes one record per word

tableA
id,dateAddID,word

here is the data:

LISTDATEID       NEGITIVEWORD
1       31       private rentals
2       31       condos
3       31       condo
4       31       printing
5       31       print
6       31       plateau
7       31       schools
8       31       school
9       31       biography
10       31       coupons

then in tableb
I have:
LISTDATEID       NEGITIVEWORD
1       31       rentals
2       31       condos
3       31       condo
4       31       printing
5       31       print
6       31       plateau
7       31       schools
8       31       school
9       31       coupons

0
 
LVL 52

Accepted Solution

by:
_agx_ earned 250 total points
ID: 34892238
Thanks.  

A simple JOIN between the tables should work.

SELECT a.NegitiveWord
FROM    TableA a INNER JOIN TableB b
             ON a.ListDateID = b.ListDateID AND a.NegitiveWord = b.NegitiveWord
WHERE  a.LISTDATEID = 31 <!--- or whatever list id you want --->
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34892268
id,dateAddID,word

In the sample it looks like the shared column is "LISTDATEID". So you may need to adjust the column names.
0
 
LVL 3

Expert Comment

by:sajayc
ID: 34892331
Hi,

Query B can be stored as:
<cfset listvariablename = ValueList(queryb.columnn)>

Then you can loop over Query A value to see if it is in the list.
<cfif ListFind(listvariablename , querya.value, ",")>Do something if it matches.</cfif>

Hope this helps.

0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question