If I understand you correctly, you want to do two things:

1. List one row per person, containing up to three different contact
numbers: one "Home", one "Cell" and one "Office".

2. List only those persons where there exists at list one other person
with the same contact number (regardless of contact type).

If this is what you want, I think it would look like this:

select P.Code,
P.Name,
CHome.Contact_Value Home,
CCell.Contact_Value Cell,
COffice.Contact_Value Office
from P
left join C CHome
on CHome.Code = P.Code
and CHome.Contact_Type = 'Home'
left join C CCell
on CCell.Code = P.Code
and CCell.Contact_Type = 'Cell'
left join C COffice
on COffice.Code = P.Code
and COffice.Contact_Type = 'Office'
where exists (
select 1
from C CDup
where CDup.Code <> P.Code
and (CDup.Contact_Value = CHome.Contact_Value
or CDup.Contact_Value = CCell.Contact_Value
or CDup.Contact_Value = COffice.Contact_Value)
)


Untested.

Kjell

Rajesh Punjabi wrote:

> 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

--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64