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

[Scott Stanfield]

Thanks.  You folks are great.   Yes they are integers.   There are a few reasons that I haven't used PK-FK on the table.  I have a few procedures that load data into the table from another database and with PK-FK it will simply not load the record if it doesn't have a match.   I'll try the ctxt and see it that works.  I figure it must be adding them together as well.   Scott         From: rbase-l@rbase.com [mailto:[EMAIL PROTECTED] On Behalf Of Dawn Oakes Sent: Wednesday, March 02, 2005 3:59 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Finding Records in a table that violate the one-to-many relationship 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?