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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top