Did you try the query I posted yesterday?

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)
)

Kjell

Rajesh Punjabi wrote:

> Hi,
>
> Thanks for the pointers. Here is my query :
>
> Select P.Code, P.Name ,
> (Select first 1 C.CONTACT_VALUE from C where C.Code = P.Code and
> C.CONTACT_TYPE = 'Home') Home_No,
> (Select first 1 C.CONTACT_VALUE from C where C.Code = P.Code and
> C.CONTACT_TYPE = 'Cell') Cell_No,
> (Select first 1 C.CONTACT_VALUE from C where C.Code = P.Code and
> C.CONTACT_TYPE = 'Office') Off_No
> from P
>
> Now the part causing the confusion is how does one write the where part
> so that I get only those records which are duplicate in my particular
> design of the db structure since there is no count(*) in my particular
> queries select clause but it needs to be there in my where or having clause.

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