Solved

Comparing Two list with coldfusion

Posted on 2011-02-14
7
537 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ColdFusion Need assistance updating my legacy zip function 13 44
http to https 3 70
Internal to DMZ IIS Authentication. 3 27
SSL Certificate for IIS7 Site 2 59
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

828 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