Hey all,
"Simple" question here. I have two identically structured tables whose contents represent *.DBF structure information (COPY STRU EXTE TO file) of two similar file-directories. The unique identities of these records would be 'TABLE_NAME+FIELD_NAME'. I need to isolate the records that have changed between the tables, and the records that represent orphaned files.
Right now, my co-worker is using an exhaustive scheme of indexing, relationships, and scanning. I'm sure there must be a simple SQL-SELECT that will solve this problem.
After combining the tables using UNION ALL, I know that for each record that has an exact duplicate, its TABLE_NAME DBF's FIELD_NAME field has not had any structural changes, and can be considered unchanged between directories. The records who don't match exactly, but share 'TABLE_NAME+FIELD_NAME' are fields in their parent tables that differ in structure. Orphaned FIELD_NAME fields (where the TABLE_NAME exists in both directories) represent added/deleted fields in their respective TABLE_NAME table's structure.
So, first I want to query the UNION ALL of these tables to find the records that have no exact matches. I suppose those would be records with a frequency of 1. Then I need to further isolate records of this result with similar 'TABLE_NAME's and do another frequency analysis, and isolate records where a frequency of 1 means it's an orphaned table. Next, I need to do a similar analysis on 'TABLE_NAME+FILED_NAME' to isolate the added/removed fields from fields that have had some sort of structural change.
Will someone please give me some insight as to constructing a 3-tiered SQL-SELECT statement of this nature?
Thanks!
John MR Fitzgerald
Order Inc.