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.

Once more my DB Structure is as follows :

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

Any pointers to this now ?

Awaiting your response.

Warm regards,


RP

Adam wrote:

>RP,
>
>I can think of a quick way to do it but I imagine the performance may
>be slow on large tables. Join may actually be left join, it depends on
>your business rules.
>
>select p.code, p.name, c.home, c.cell, c.office, count(*)
>from p
>join c on (p.code=c.code)
>group by p.code, p.name, c.home, c.cell, c.office
>having count(*) > 1
>
>Adam
>
>