Hi all,

I have 2 tables v4 (FieldsData1 and FieldsData2) and they are
identical in structure (have 16 fields). The table field names are
based on the DBSYS field names ie Position, FieldName, DataType, Size,
Required,.....  and an extra two fields called TableName and
DataFolder.

I want to find the unique records between these 2 tables. I can find
unique FieldName records by   

<
SELECT DataFolder, TableName, FieldName, Size FROM FieldsData1 WHERE
FieldName NOT IN (SELECT FieldName FROM
FieldsData2)

UNION

SELECT DataFolder, TableName, FieldName, Size FROM FieldsData2 WHERE
FieldName NOT IN (SELECT FieldName FROM
FieldsData1)
>

but how do I find the differences in the other fields like DataType,
Size,... in these 2 tables records? I could do an SQL for every record
field (16 of them) but is there a better way?

Regards,
Pat

Have you thought of using the DBISAM system field "RecordHash" ?

As per manual "RecordHash is an MD5 binary value (16 bytes) that represents
the hash of a given record".

As i recall it does not work for BLOBs but might be of value : in theory all
you'd need to do is compare the RecordHash for the matching rows between 2
tables. Might be worth a try.

Raul


"Pat" <pat@downunder.com> wrote in message
news:ucd335l08ialvv5s5j7jbtgotvnlh5i7bk@4ax.com...
> Hi all,
>
> I have 2 tables v4 (FieldsData1 and FieldsData2) and they are
> identical in structure (have 16 fields). The table field names are
> based on the DBSYS field names ie Position, FieldName, DataType, Size,
> Required,.....  and an extra two fields called TableName and
> DataFolder.
>
> I want to find the unique records between these 2 tables. I can find
> unique FieldName records by
>
> <
> SELECT DataFolder, TableName, FieldName, Size FROM FieldsData1 WHERE
> FieldName NOT IN (SELECT FieldName FROM
> FieldsData2)
>
> UNION
>
> SELECT DataFolder, TableName, FieldName, Size FROM FieldsData2 WHERE
> FieldName NOT IN (SELECT FieldName FROM
> FieldsData1)
>>
>
> but how do I find the differences in the other fields like DataType,
> Size,... in these 2 tables records? I could do an SQL for every record
> field (16 of them) but is there a better way?
>
> Regards,
> Pat
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 4149 (20090611) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>



__________ Information from ESET NOD32 Antivirus, version of virus signature database 4149 (20090611) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

>I want to find the unique records between these 2 tables.

maybe UNIQUE is not quite right

a bit more info, these 2 tables (FieldsData1 and FieldsData2) store
the table field information from 2 data folders. My aim is to compare
the 2 data folders and find:

- what tables are in one data folder and NOT the other
- with common tables in both data folders, what fields are different
in these tables

The code snippet I showed tells me what TABLES & FIELDS differ between
the 2 folders but it does not show what, say, String fields are
different sizes.

"Pat" <pat@downunder.com> wrote in message
newsi4359vue4tjm6qvaqoq48galqav6mbji@4ax.com...
> >I want to find the unique records between these 2 tables.
>
> maybe UNIQUE is not quite right
>
> a bit more info, these 2 tables (FieldsData1 and FieldsData2) store
> the table field information from 2 data folders. My aim is to compare
> the 2 data folders and find:
>
> - what tables are in one data folder and NOT the other
> - with common tables in both data folders, what fields are different
> in these tables
>
> The code snippet I showed tells me what TABLES & FIELDS differ between
> the 2 folders but it does not show what, say, String fields are
> different sizes.


How about something like this?

SELECT ... FROM FieldsData1 T1
JOIN FieldsData2 T2 on (T1.TableName = T2.TableName)
WHERE ((T1.Field1 <> T2.Field1) OR (T1.Field2 <> T2.Field2) ... )

Robert

will check it out, thanks

>How about something like this?
>
>SELECT ... FROM FieldsData1 T1
>JOIN FieldsData2 T2 on (T1.TableName = T2.TableName)
>WHERE ((T1.Field1 <> T2.Field1) OR (T1.Field2 <> T2.Field2) ... )

looks good

thanks