Two tables, break inside loop.

[DeZiner]

table1

record card amount match
1 1234 5
2 3456 6
3 1234 5


table 2

record card amount match
1 741 6
2 1234 5
3 1234 5
4 1234 5

I have these two tables. I need to make a one on one match from records in table 1 to records in table two. In the above case the records should match as table1 1 to table 2 2, table 1 3 to table 2 3. No more matches should be made. I don't have the code I have used available at the moment. The trouble- I am running two loops. The internal loop that makes the match needs to stop once a match is found so it doesn't match table 1 record 1 with table 2 records 2 3 and 4.
Table one as you see may contain duplocate records as will table 2. I loop through the records in table 1 to find matches one on one in table 2.

Hope this explanation is ok. I can add more columns if needed.

Also, if this works out to be possible any ideas what the processing time may be for 2500 records in table 1 to find matches out of 65000 transactions in table 2?

Thanks in advance. Banging my head for a week. Just help me stop the inside loop when it finds a match DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic

 

[CFDude]

If I may, it may be better to step back a bit and look at the design of the database. What database are you using and can you give some more details on what you are trying to accomplish?

Is there a reason you need 2 separate tables? It looks like it is holding the same exact thing, but separated for some reason -- could you not have a single table with
RecordID, card, amount, and then add a column for the source or what it is for?

 

[DeZiner]

Basically these are sale transactions. One table of orders placed, and a second table of known 'charged' orders. I need to verify 1.) Every card in table one was charged by finding a match in table two. 2.) Each in table 1 was only charged once not twice, as unfortunetley this is common. If we catch we can refund before getting notified.

If it is listed in table 1 once, it should be in table 2 once. If not in table 2 I know the order still needs to be processed, and if in table 2 once we are good and if in table 2 2,3 or 4 times we need to refund. I have info in excel and import into access.

Table 1 has a Order ID associated with it I can use as a key field. Table 2 has no key so I create a generic one. DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic

 

[DeZiner]

Hope this is enough info. DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic

 

[sshhz]

If that is the case, you can store these two tables into one table which has a flag or a boolean to indicate whether it's processed or not.

record card amount processStatus
1 1234 5 n
2 3456 6 y
3 1234 5 n

Or you can ve other fields like PayStatus, ChargeStatus, and so on ...

so, if you want to list those that are charged ... could try
select field1, field2 From table1 where ChargeStatus = 'y'
or if you want to know whether the order is processed ..
select field1, field2 From table1 where processStatus = 'y'

It save the trouble to match two tables as it could uses more time to match these.

Just another thought of doing this. Good Luck.

-sshhz-

 

[CFDude]

Deziner,
Sorry I was not able to get back to you -- basically, what sshhz posted was exactly what I was going to suggest ast well -- keep it all in one table with a column for status, etc.

Is this not possible to do or are you stuck with the design it is in now?

Tim P.

 

[DeZiner]

I am pulling the orders and the charged detail from seperate areas. If in one table I would have a hard time differentiating which were from the should be processed or have been processed table. I have it working now except that I need to break out of the inside loop when it finds the first match. DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic

 

[sshhz]

DeZiner,

If you view from one time and playing around with those cfquery, you will see a cleaner record rather than in the database side. From this approach, you can save your trouble matching inside the loop and this will slow down the performance where it requires more time to do the matching.

If the order is processed, sure there is a flag value (one of the field) updated from unprocess to process. Same goes to charged or not charged. You must draw out your business logic first, the flow of it before getting to design this table. That's my advice to you.

If you still think two tables will be easier to see those orders physically, i think you've the trouble doing the matching, then why not change this to one?? It solves all the trouble! What if you have 5 processes that need to process for the orders, will you have 5 tables to keeping each process? What if they have change the process from 5 to 3, can the existing table design still can cope for the change? All these questions must be thought in your mind before designing your database structure.

Good Luck.

-sshhz-

 

[tlhawkins]

This is probably kind of late but...

if you want to just do it your way and you need a way out of your inside loop you can do this:
<cfset jump=&quot;true&quot;
<CFLOOP Condition = &quot;YourLoopStuff&quot; AND jump=&quot;false&quot;>

test for matching if matches do what you need to then
<cfset jump=&quot;true&quot;>
</cfloop>

That will get you out of your loop without the entire recordset being tested. If you're using a <CFOUTPUT ...> loop it isn't very hard to change it to <cfloop> I think you loose a little speed doing that but it does what you need without having to re-do your Database.

Have fun.