Board index » delphi » Finding Duplicate Child Records

Finding Duplicate Child Records

In my ib 6 database, I have two tables...A master table and a child table
(please note this is a simplified version of the db).

master table
    ID : integer

child
   ID : integer
   Last_Name : string(35)

I would like to return IDs for the child records that match each other....
i.e.,
  Master Table
        1
        2
        3
        4

  Child Table
        1    'MILLER'
        1    'BARNES'
        2    'CARP'
        3    'MILLER'
        3    'BARNES'
        4    'MARTIN'

So the statement would return IDs 1 and 3....

Any help is greatly appreciated!
Thanks,
Jeff

 

Re:Finding Duplicate Child Records


Try something like this:

select c1.id
from child c1
where 1 <
  (select count(c2.LastName)
   from child c2
   where (LastName is not null)
    and (LastName <> '' )
    and c2.LastName = c1.LastName
)

Quote
"Jeff Cope" <JeffNOSPAMC...@NOSPAMlllow.com> wrote in message

news:3d1a2b58$1_2@dnews...
Quote
> In my ib 6 database, I have two tables...A master table and a child table
> (please note this is a simplified version of the db).

> master table
>     ID : integer

> child
>    ID : integer
>    Last_Name : string(35)

> I would like to return IDs for the child records that match each other....
> i.e.,
>   Master Table
>         1
>         2
>         3
>         4

>   Child Table
>         1    'MILLER'
>         1    'BARNES'
>         2    'CARP'
>         3    'MILLER'
>         3    'BARNES'
>         4    'MARTIN'

> So the statement would return IDs 1 and 3....

> Any help is greatly appreciated!
> Thanks,
> Jeff

Other Threads