[RBASE-L] - RE: Finding Records in a table that violate the one-to-many relationship

[Dawn Oakes]

Are plantno, custid or jobno integers?   If so, try converting to text first.   edi all fro salestrans where ((ctxt(plantno)) + (ctxt(custid)) + (ctxt(jobno))) not in (select ((ctxt(plantno)) + (ctxt(custid)) + (ctxt(jobno)) from job)   With integers, I think they would get added together.  Considering 2 + 2 + 5 = 9 and 1 + 2 + 6 = 9, you're bound to get inaccurate results a lot of the time.    Why not put a multi-column PK - FK on the tables?  That would keep the rows in job from being deleted while there were still rows in salestrans.   Dawn From: Scott Stanfield [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 02, 2005 3:45 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Finding Records in a table that violate the one-to-many relationship Maybe someone can help me with this situation.  I issued the following command:   R>edit all from salestrans where plantno+custid+jobno not in (select plantno+custid+jobno from job)   The key to the table job is the plant number, customer number, and the job number which make it unique.   Any records in the salestrans must have a corresponding match in the job table (many-to-one relationship).   What I wanted to do is check the salestrans to make sure a record in the job table had not been deleted, leaving an unmatched record(s) in the salestrans table.  This command has been sucessful in finding such strays, but it failed to find a problem this time.    I thought by saying plantno+custid+jobno was a way to use a multi-column key in a command.  Maybe it is literally adding the values?