Thank you to all who looked at this before. The table structure can change (I can add/delete columns) but the data is pulled after the fact from two seperate sources. The suggestions of one table can be done as well but I am not seeing how that will give me the desired reults.
The run down:
I want to select one record at a time from table 1. Take the results and look for 1 single match from table 2. If there are several matches in table 2, I only want one of them. Mark the records in both tables as have been found. Start over by getting the next row from table 1. If the following rows from table 1 find a match in table 2 that have already had a match, ignore it and look for a different row to match.
Looking for a one on one relationship. Both tables may have duplicate records. When the results are done I will query the first table for any records that did not have a match from table 1 and query table 2 for any records that have no corresponding match from table 1.
The Tables
table1
record_number card_number amount foundIt
1 1234 5
2 3456 6
3 1234 5
table 2
record_number card_number amount foundIt
1 741 6
2 1234 5
3 1234 5
4 1234 5
The results should be:
Table 1 record 1 = table 2 record 2
Table 1 record 3 = table 2 record 3
And that's it
The Trouble
The inside loop finds all records that match and marks them all. Remember, I only want it to match one that has not already had a match. When the loop goes back to the outside loop, it completely skips the inside loop.
I need to mark only 1 record and then ignore the record in future queries. And stop the inside loop when it finds a match and move on to the next record in table 1.
The Code:
<!--- Set How many records are in the Reports table--->
<cfquery name="getcount" datasource="Datasource">
SELECT *
FROM reports
</cfquery>
<!--- Set the number of records in the MSA Table --->
<cfquery name="getcountM" datasource="Datasource">
SELECT *
FROM MSA
</cfquery>
<!--- Start our loop from Reports. Pull one record at a time to check against MSA--->
<cfloop index="Idx" from=1 to=#getcount.recordcount#>
<!--- Set our inside loop condition for each new record from reports--->
<!--- If the inside loop finds a match it will set foundIt to 'r' and start the outside loop over again--->
<!--- When the outside loop starts over it will set the condition back to 'w' for the inside loop--->
<cfparam name=foundIt default=w>
<!--- Grab a row from Reports--->
<cfquery name="getrow" datasource="Datasource">
SELECT Distinctrow card_number, amount, record_number, foundIt
FROM Reports
WHERE record_number=#Idx#
</cfquery>
<!--- Set an alias name for cardnumber & amount from the Reports Table--->
<cfset queryCard=#getrow.card_number#>
<cfset queryAmount=#getrow.amount#>
<!--- Start our inside loop--->
<cfloop Condition="foundIt EQ 'w'">
<cfquery name="makematch" datasource="Datasource">
SELECT card_number, amount, record_number, foundIt
FROM MSA
WHERE card_number='#queryCard#'
AND amount='#queryAmount#'
AND foundIt='w'
</cfquery>
<cfoutput query="makematch">
<cfif #makematch.recordcount# GT 0>
<cfset foundIt='r'>
<cfset foundrow=#makematch.record_number#>
<cfquery name="markit" datasource="Datasource">
UPDATE MSA
SET foundIt =#Idx#
WHERE record_number=#foundrow#
</cfquery>
<cfquery name=markReports datasource="Datasource">
UPDATE Reports
SET foundIt = #foundrow#
WHERE record_number=#Idx#
</cfquery>
<cfelse>
</cfif>
</cfoutput>
</cfloop>
</cfloop>
<!--- *********************** End ***************************--->
<body>
<cfquery name="reportsout" datasource="Datasource">
SELECT *
FROM Reports
</cfquery>
<cfquery name="msaout" datasource="Datasource">
SELECT *
FROM MSA
</cfquery>
<table width="400" border="1">
<tr>
<td colspan="2">Reports Card Number: <cfoutput>#queryCard#</cfoutput></td>
</tr>
<tr>
<td colspan="2">Reports Amount: <cfoutput>#queryAmount#</cfoutput></td>
</tr>
<tr>
<td colspan="2">Records That Matched: <cfoutput>#makematch.recordcount#</cfoutput></td>
</tr>
<tr>
<td colspan="2">MSA Rows to Loop: <cfoutput>#getcountM.recordCount#</cfoutput></td>
</tr>
<tr>
<td colspan="2"><cfoutput query="getrow" maxrows="1">Reports Rows to Loop: #getcount.recordcount#</cfoutput></td>
</tr>
</table>
<p>
<cfquery name="rout" datasource="Datasource">
SELECT *
FROM Reports
</cfquery>
<cfquery name="mout" datasource="Datasource">
SELECT *
FROM MSA
</cfquery>
<table width="400" border=1>
<tr>
<td colspan="4">Reports Table:</td>
</tr>
<tr>
<td>Record Number:</td>
<td>Card Number:</td>
<td>Amount:</td>
<td>Found It:</td>
</tr>
<cfoutput query="rout">
<tr>
<td>#record_number#</td>
<td>#card_number#</td>
<td>#amount#</td>
<td>#foundIt#</td>
</tr>
</cfoutput>
</table>
<p>
<table width="400" border=1>
<tr>
<td colspan="4">MSA Table:</td>
</tr>
<tr>
<td>Record Number:</td>
<td>Card Number:</td>
<td>Amount:</td>
<td>Found It:</td>
</tr>
<cfoutput query="mout">
<tr>
<td>#record_number#</td>
<td>#card_number#</td>
<td>#amount#</td>
<td>#foundIt#</td>
</tr>
</cfoutput>
</table>
</body>
DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic
The run down:
I want to select one record at a time from table 1. Take the results and look for 1 single match from table 2. If there are several matches in table 2, I only want one of them. Mark the records in both tables as have been found. Start over by getting the next row from table 1. If the following rows from table 1 find a match in table 2 that have already had a match, ignore it and look for a different row to match.
Looking for a one on one relationship. Both tables may have duplicate records. When the results are done I will query the first table for any records that did not have a match from table 1 and query table 2 for any records that have no corresponding match from table 1.
The Tables
table1
record_number card_number amount foundIt
1 1234 5
2 3456 6
3 1234 5
table 2
record_number card_number amount foundIt
1 741 6
2 1234 5
3 1234 5
4 1234 5
The results should be:
Table 1 record 1 = table 2 record 2
Table 1 record 3 = table 2 record 3
And that's it
The Trouble
The inside loop finds all records that match and marks them all. Remember, I only want it to match one that has not already had a match. When the loop goes back to the outside loop, it completely skips the inside loop.
I need to mark only 1 record and then ignore the record in future queries. And stop the inside loop when it finds a match and move on to the next record in table 1.
The Code:
<!--- Set How many records are in the Reports table--->
<cfquery name="getcount" datasource="Datasource">
SELECT *
FROM reports
</cfquery>
<!--- Set the number of records in the MSA Table --->
<cfquery name="getcountM" datasource="Datasource">
SELECT *
FROM MSA
</cfquery>
<!--- Start our loop from Reports. Pull one record at a time to check against MSA--->
<cfloop index="Idx" from=1 to=#getcount.recordcount#>
<!--- Set our inside loop condition for each new record from reports--->
<!--- If the inside loop finds a match it will set foundIt to 'r' and start the outside loop over again--->
<!--- When the outside loop starts over it will set the condition back to 'w' for the inside loop--->
<cfparam name=foundIt default=w>
<!--- Grab a row from Reports--->
<cfquery name="getrow" datasource="Datasource">
SELECT Distinctrow card_number, amount, record_number, foundIt
FROM Reports
WHERE record_number=#Idx#
</cfquery>
<!--- Set an alias name for cardnumber & amount from the Reports Table--->
<cfset queryCard=#getrow.card_number#>
<cfset queryAmount=#getrow.amount#>
<!--- Start our inside loop--->
<cfloop Condition="foundIt EQ 'w'">
<cfquery name="makematch" datasource="Datasource">
SELECT card_number, amount, record_number, foundIt
FROM MSA
WHERE card_number='#queryCard#'
AND amount='#queryAmount#'
AND foundIt='w'
</cfquery>
<cfoutput query="makematch">
<cfif #makematch.recordcount# GT 0>
<cfset foundIt='r'>
<cfset foundrow=#makematch.record_number#>
<cfquery name="markit" datasource="Datasource">
UPDATE MSA
SET foundIt =#Idx#
WHERE record_number=#foundrow#
</cfquery>
<cfquery name=markReports datasource="Datasource">
UPDATE Reports
SET foundIt = #foundrow#
WHERE record_number=#Idx#
</cfquery>
<cfelse>
</cfif>
</cfoutput>
</cfloop>
</cfloop>
<!--- *********************** End ***************************--->
<body>
<cfquery name="reportsout" datasource="Datasource">
SELECT *
FROM Reports
</cfquery>
<cfquery name="msaout" datasource="Datasource">
SELECT *
FROM MSA
</cfquery>
<table width="400" border="1">
<tr>
<td colspan="2">Reports Card Number: <cfoutput>#queryCard#</cfoutput></td>
</tr>
<tr>
<td colspan="2">Reports Amount: <cfoutput>#queryAmount#</cfoutput></td>
</tr>
<tr>
<td colspan="2">Records That Matched: <cfoutput>#makematch.recordcount#</cfoutput></td>
</tr>
<tr>
<td colspan="2">MSA Rows to Loop: <cfoutput>#getcountM.recordCount#</cfoutput></td>
</tr>
<tr>
<td colspan="2"><cfoutput query="getrow" maxrows="1">Reports Rows to Loop: #getcount.recordcount#</cfoutput></td>
</tr>
</table>
<p>
<cfquery name="rout" datasource="Datasource">
SELECT *
FROM Reports
</cfquery>
<cfquery name="mout" datasource="Datasource">
SELECT *
FROM MSA
</cfquery>
<table width="400" border=1>
<tr>
<td colspan="4">Reports Table:</td>
</tr>
<tr>
<td>Record Number:</td>
<td>Card Number:</td>
<td>Amount:</td>
<td>Found It:</td>
</tr>
<cfoutput query="rout">
<tr>
<td>#record_number#</td>
<td>#card_number#</td>
<td>#amount#</td>
<td>#foundIt#</td>
</tr>
</cfoutput>
</table>
<p>
<table width="400" border=1>
<tr>
<td colspan="4">MSA Table:</td>
</tr>
<tr>
<td>Record Number:</td>
<td>Card Number:</td>
<td>Amount:</td>
<td>Found It:</td>
</tr>
<cfoutput query="mout">
<tr>
<td>#record_number#</td>
<td>#card_number#</td>
<td>#amount#</td>
<td>#foundIt#</td>
</tr>
</cfoutput>
</table>
</body>
DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic