Comparing Two list with coldfusion

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?

timothylwatsonAsked:
Who is Participating?
 
_agx_Commented:
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
 
_agx_Commented:
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
 
timothylwatsonAuthor Commented:
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
How do you know if your security is working?

Protecting your business doesn’t have to mean sifting through endless alerts and notifications. With WatchGuard Total Security Suite, you can feel confident that your business is secure, meaning you can get back to the things that have been sitting on your to-do list.

 
_agx_Commented:
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
 
timothylwatsonAuthor Commented:
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
 
_agx_Commented:
id,dateAddID,word

In the sample it looks like the shared column is "LISTDATEID". So you may need to adjust the column names.
0
 
sajaycCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.