timothylwatson
asked on
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?
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?
ASKER
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.
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"
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"
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
id,dateAddID,word
In the sample it looks like the shared column is "LISTDATEID". So you may need to adjust the column names.
In the sample it looks like the shared column is "LISTDATEID". So you may need to adjust the column names.
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.
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.