[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 542
  • Last Modified:

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?

0
timothylwatson
Asked:
timothylwatson
  • 4
  • 2
1 Solution
 
_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
 
_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
Evaluating UTMs? Here's what you need to know!

Evaluating a UTM appliance and vendor can prove to be an overwhelming exercise.  How can you make sure that you're getting the security that your organization needs without breaking the bank? Check out our UTM Buyer's Guide for more information on what you should be looking for!

 
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:
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:
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

Featured Post

Evaluating UTMs? Here's what you need to know!

Evaluating a UTM appliance and vendor can prove to be an overwhelming exercise.  How can you make sure that you're getting the security that your organization needs without breaking the bank? Check out our UTM Buyer's Guide for more information on what you should be looking for!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now