Unmatched records 1

[crossface]

I have an SQL where I have Table A and Table B.

I want to find the records in Table A which do not match with table B

Is there a way to do this?

Thanks

Kevin Cotter
Byron Schools

 

[mpastore]

Does each table share the same primary key?

Mike Pastore

Hats off to (Roy) Harper

 

[crossface]

Yes

Each table has a field named STUID

Kevin Cotter
Byron Schools

 

[mpastore]

If you just want to know what table a records are not in table b


select * from tablea where stuid not in (select stuid from tableb)


...do the opposite to find tableb records not in tablea.

If you want to find records where the stuid's match between tables but there is at least one field-level difference, this is a bit more involved. You need to assume that each table has the same record structure. Let me know if you need to do this.

Mike Pastore

Hats off to (Roy) Harper

 

[crossface]

I am not doing a good job of explaining myself.

Table A (Students) has demographic information about students in our district.

Table B (Attend) has attendance(absence) information about many of the students.

The field which relates both tables is stuid

My SQL is something like this for my report

SELECT * from Students, Attend;
WHERE Students.StuiID=Attend.StuID;
Into Cursor TempReport

About 1700 of the 1800 students have attendance data or records in the attend.dbf. 100 students do not appear in the report because their records are unmatched or do not have any records in the attend.dbf. I want to use SQL to find the unmatched records.

I can then create the same report and indicate that these students have no attendance data.

I hope this makes better sense.

Thanks again

Kevin Cotter
Byron Schools

 

[jimstarr]

Hi Kevin,

You may want something like:

SELECT * from Students FULL JOIN Attend;
ON Students.StuiID = Attend.StuID;
Into Cursor TempReport

On mismatched records the attendance fields will be filled in as .NULL.

Jim

 

[crossface]

Thanks!!!!

Kevin Cotter
Byron Schools