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