Comparing whole records

[tcstudio]

I need to check one table against another so what I want to do is select record 1 in table A and then compare that record to All records in Table B. Then I want to output any records that do not have a match?

 

[StewartUK]

tcstudio,

Is there a field in both tables that you can use to match them?

If so you could try using SQL to do this.

For example:

SELECT * FROM TableA WHERE KeyField NOT IN (SELECT KeyField FROM TableB) INTO TABLE OtherTable

You can use the SQL keyword EXISTS :

SELECT * FROM TableA WHERE EXISTS (SELECT * FROM TableB) INTO TABLE OtherTable

Hope that helps,

Stewart

 

[repete]

hi tcstudio
test the following example with 3 character fields, is that what you are after ?

* create 2 tables with data
numofrecs = 2
create table studioa (field1 c(50), field2 c(50), field3 c(50))
do populate
create table studiob (field1 c(50), field2 c(50), field3 c(50))
append from studioa
do populate


* output records that do not have a match
close data
create table studioc (field1 c(50), field2 c(50), field3 c(50)) && to hold answers
use studioa in 0 && from table creation above
use studiob in 0 && from table creation above

select studioa
index on field1+field2+field3 tag seekstr additive

select studiob
scan
scatter memvar
m.seekstr = field1+field2+field3
if not seek(m.seekstr,"studioa&quot
insert into studioc values (m.field1, m.field2, m.field3)
endif
endscan

* browse your data files


procedure populate
for i = 1 to numofrecs
m.info = '_'+alltrim(str(i))+'_'+alias()
insert into (alias()) values ( 'f1'+m.info, 'f2'+m.info, 'f3'+m.info )
endfor
return



Pete Bloomfield
Down Under