Hi Everyone,

I have the following two tables and I need to run a query for
deduplicating the data. I want to show a list of duplicates. Here are
the two tables.

Table "P"
Code - Integer
Name - Varchar(200)

Sample Data:
1 - Tom Cruise
2 - Rudolf Reindeer
3 - Helen Borrie
4 - R P
5 - Santa Claus

Table "C"
Code - Integer
Contact_Type - Varchar(200)
Contact_Value - Varchar(200)
Status - Varchar(1)

Sample Data :
1 - Home - 987654 - A
1 - Cell - 787878 - A
3 - Office - 123456- A
4 - Cell - 987654 - A
5 - Home - 787878 - A

Now P is the master and C is the detail table. What I want to know is
the list of people in P who have the similar contact details.

The result should be like this
Code - Name - Home - Cell - Office
1 - Tom Cruise - 987654 - 787878 - null
4 - R P - null - 987654 - null
5 - Santa Claus - 787878 - null - null

I can use subqueries to get the home - cell - office data in the select
part itself. The contact detail duplicate part has me stumped. The trick
is in how one writes the where clause I guess. Can anyone help ?

Warm regards,


RP