The ultimate goal is to find duplicates. The reason we need to take relationship and phone into account because that is where most of the duplicates are.

For example:

Patient...

I ran the query against real data, and noticed the query does not take into account relationship_type. For example, mother and father can both have same phone. In that case,...

I agree, but records entered many times without checking if child's parent is there or not.

I am verifying the query against real data, and let you know if I...

Thank you SO VERY much, it worked...

Here is an updated schema with better data to generate OP

Kindly please get the O/P as following

PersonName PatientID PatientName Phone Relation

In the example I provided, the o/p should list one...

Thank you so much, attached is a schema sample with some data.

Please disregard my topic - I meant same person with name typo... I tried to edit the topic, but I was not successful.

Update: it turned out that we had our transaction log job was starting every single minute, even though the scheduler is set to every 10 min, causing the history tables...

Luis -

I completely forgot to update the post, thank you for your great solution using outer apply. Your answer helped me fix my query. I highly appreciate it.

Thanks again.

Lava

Lempster -

Thank you for the info, I will keep this in mind. I do not recall what security options I had setup when I recreated the linked server.

Thank you everyone for your input, I am going to test shortly and let you know ..

Hi Lempster -

Last time I created a linked server from generated script, I did not have to reenter password. I used SQL 2008.

The way I would go about it is by generating create script of the linked server in a new window, modify the script to include the modification you need, delete...

Many thanks to you, it worked. Great help!

Sorry for the late reply, I got busy with other things... Attached is a test file which includes a sample script. The goal is to list person information such as...