Suppose that there are two tables: TXN and TXN_DEAL. The latter is a child or sub-type table. TXN has composite keys -- five of them to be exact, which, of course, are repeated in the child table. I am trying to find multiple occurrences in child. In other words, I want to find TXN records that are associated to multiple TXN_DEAL records and the number (count) of TXN_DEAL records associated to them. How do I do that in SQL?

    Requires Free Membership to View

So, something like this:

PK col1
PK col2
PK col3
PK col4
PK col5

PK col1 FK
PK col2 FK
PK col3 FK
PK col4 FK
PK col5 FK
PK somecolumn

Notice how the child table has five foreign key columns, the same ones as in the TXN table, but an additional sixth column in the primary key. That sixth column is necessary because if there were only five columns in the primary key for TXN_DEAL, there would only be one row per key possible. You said you're looking for duplicates, which means that the five columns alone are not the primary key.

What we want to do now is simply count TXN_DEAL rows which are duplicated on the five FKs alone. This can be achieved with COUNT and GROUP BY, with HAVING to filter out the singletons. Then we simply join the TXN table to the dupes query as a derived table.

select TXN.col1
     , TXN.col2
     , TXN.col3
     , TXN.col4
     , TXN.col5
     , TXN.datacol6
     , TXN.datacol7  
     , TXN_DEAL_dupes.rows  as TXN_DEAL_rows
  from TXN
  join (
       select col1
            , col2
            , col3
            , col4
            , col5   
            , count(*) as rows
         from TXN_DEAL
           by col1
            , col2
            , col3
            , col4
            , col5
       having count(*) > 1
       ) as TXN_DEAL_dupes
    on TXN.col1 = TXN_DEAL_dupes.col1
   and TXN.col2 = TXN_DEAL_dupes.col2
   and TXN.col3 = TXN_DEAL_dupes.col3
   and TXN.col4 = TXN_DEAL_dupes.col4
   and TXN.col5 = TXN_DEAL_dupes.col5

Joining to a derived table is almost always the simplest method when compound keys are involved, and is essential when a count is required at the same time.

This was first published in February 2006

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: