one-to-many compare problem

[BradEdwards]

I have a system in which I scan in signature cards (sigcards). These sig cards have keywords on them called CIFKey. They can have one or more CIFKeys on them. The docnum for the sig card is stored in one table and the keywords are stored in another and linked by docnum. So I have a one-to-many relationship. Here's my problem. If I scan in a sigcard (SigCard 01) which has 2 CIFKeys on it (CIF01 and CIF02) and then 6 months later I scan in a new sig card with the same CIFKeys (CIF01 and CIF02), I need to mark the original sig card as inactive and mark the current one as active. I need help in writing a query to find out which sigcards have duplicate CIFkeys so I can mark the most current one as active and mark all others as inactive. Following is the basic table structure:

SigCardTable
docnum int unique primary key
docdate datetime
status varchar

KeyWordsTable (1-to-many relationship with SigCardTable)
docnum int
CIFKey varchar

I'm assuming I need to use a junction table but not sure how. Any help would be most appreciated. Thanks.

 

[johpje]

Before you insert the new card into the db, just run the folowing update statement:

UPDATE SigCardTable SET status = inactive
 WHERE docnum IN (
  SELECT SigCardTable.docnum
  FROM SigCardTable INNER JOIN KeyWordsTable
      ON SigCardTable.docnum = KeyWordsTable.docnum
   WHERE CIFKey = @CIF01 OR CIFKey = @CIF02
  GROUP BY SigCardTable.docnum
   HAVING COUNT(SigCardTable.docnum) = 2)

this will only update the cards that have the exact same combination of CIFKeys.

cheers,
Johpje

 

[BradEdwards]

Johpje, thanks for the reply, however that will not work for me. The software used to scan is third-party, I'm just updating records in the table because the software does not handle this. We will have a process that runs at night to do handle all of the marking of inactive/active status. Also the number of CIFkeys on a document will vary. It could be 1 to 10 or more. And last but not least I mis-stated my table relationships. There is a 1-to-many relationship but the SigCardTable will have 1 record and the KeywordsTable could have many. Thanks Johpje.