matching records in two diff tables then

Results 1 to 2 of 2

Thread: matching records in two diff tables then

  1. #1
    Join Date
    Dec 1969

    Default matching records in two diff tables then

    Hi,<BR>I got two tables history and current. They both have same fields like pref, qual. If history = current table then i dont have to do anything else i need to insert into a different table. How can i find both tables are same or not by using a query. In each of those tables one person can enter upto 6 pref and qual. <BR><BR>Any ideas?

  2. #2
    Join Date
    Dec 1969

    Default Treat one of them as the master...

    ...table. I presume in this case it is the History table that is the master. That you look for changes in the Current table and dump those into yet another table?<BR><BR>So start by finding the changed records:<BR><BR>SELECT Current.*<BR>FROM History AS H LEFT JOIN Current AS C<BR>ON ( C.primaryKeyFld = H.primaryKeyFld <BR> AND C.fld1 = H.fld1 AND C.fld2 = H.fld2 AND ... )<BR>HAVING H.primaryKeyFld IS NOT NULL<BR><BR>See if that gets you the records you need. Do you see how it works? Until the HAVING is used, you are getting *all* records in the Current table. The ON condition is looking for identical records and, when they are found, it JOINs the corresponding History record to the Current record. If the ON condition does *NOT* find an identical record, then the Current record is joined to a NULL history record. Then the HAVING rejects all the records that *did* find matches, leaving only those that didn&#039;t!<BR><BR>Anyway, if that SELECT works, then you just have to insert those into the 3rd table. Probably as simple as<BR><BR>INSERT INTO ChangesTable<BR>SELECT above...<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts