Q

Dupes involving a compound key

Suppose that there are two tables: TXN and TXN_DEAL. The latter is a child or sub-type table. TXN has five composite keys, which 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 of TXN_DEAL records associated to them. How do I do that in SQL?

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?

So, something like this:

TXN
PK col1
PK col2
PK col3
PK col4
PK col5
   datacol6
   datacol7

   
TXN_DEAL
PK col1 FK
PK col2 FK
PK col3 FK
PK col4 FK
PK col5 FK
PK somecolumn
   datacol8
   datacol9

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
inner
  join (
       select col1
            , col2
            , col3
            , col4
            , col5   
            , count(*) as rows
         from TXN_DEAL
       group
           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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close