Merge apparent duplcate rows into 1 row?

Hi,
I have a select query that can generate apparent duplicates; this
occurs because the Histology value is determined from a table
tblSample, this may contain a number of samples for the same location
as there are different methods of obtaining samples sometimes 2 or more
method are used to back up results. The method is not important for
this table and so not shown, so showing apparent duplicates. Heres an
example of the table:
Code Date Location Histology
---
CO123 12/08/2005 Left Main Adeno
CO123 12/08/2005 Left Main Adeno
BJ234 12/08/2005 Right Main Normal
BJ234 12/08/2005 Right Lower Squamous
CH345 17/08/2005 Right Middle Normal
This is my SQL:
SELECT tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Is there a way to combine these apparent duplicate rows into one row?
Essentially doing:
If no of rows where Code, Date, Location, match > 1
Delete rows >= 2
ThanksUse a SELECT DISTINCT:
SELECT DISTINCT
tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1125055041.994620.74900@.g14g2000cwa.googlegroups.com...
Hi,
I have a select query that can generate apparent duplicates; this
occurs because the Histology value is determined from a table
tblSample, this may contain a number of samples for the same location
as there are different methods of obtaining samples sometimes 2 or more
method are used to back up results. The method is not important for
this table and so not shown, so showing apparent duplicates. Heres an
example of the table:
Code Date Location Histology
---
CO123 12/08/2005 Left Main Adeno
CO123 12/08/2005 Left Main Adeno
BJ234 12/08/2005 Right Main Normal
BJ234 12/08/2005 Right Lower Squamous
CH345 17/08/2005 Right Middle Normal
This is my SQL:
SELECT tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Is there a way to combine these apparent duplicate rows into one row?
Essentially doing:
If no of rows where Code, Date, Location, match > 1
Delete rows >= 2
Thanks|||Thanks Tom, i'm pretty new to SQL so not very familiar with the syntax
yet. Glad this one was an easy fix!|||If you're new to SQL, this is the place to hang out. :-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1125058223.511501.191000@.o13g2000cwo.googlegroups.com...
Thanks Tom, i'm pretty new to SQL so not very familiar with the syntax
yet. Glad this one was an easy fix!

Merge 2005 - A row from this table will go to only one subscription

Hello everyone.

We've been using replication since a while now and I just discovered that some user are not getting all the data they should. Let me explain the way the whole thing is configured for us.

We have a few tables that are replicated using the Merge process with filtering on the data using SUSER_SNAME() function. My users are salesrep using Laptop and they are synchronizing on demand. The merge process is configured so that a row will go to only one subscription --> Each rep has their owns customers and related tables informations.

I noticed that some users do not have locally all data (from their customers) from some tables while they should. I didn't checked in details but it is possible that these users are new reps replacing other staff that left the company. Is it possible that the data they do not receive is marked as to be previous reps owner because the replication is set as "A row from this table will go to only one subscription" ? The new rep uses the same laptop, therefore the exactly same computer name, only the username changes.

Because of this, now I'm having a bunch of conflicts when replicating because we're generating some ID based on existing data when the user enter new records in some tables; since the data is not sitting locally for them, the ID generated is not the proper one and we now have two identical ID (one on the server and one locally) which cause the conflicts to occurs.

Any helps would be appreciated. Thanks.

BUMP!

Merge 2005 - A row from this table will go to only one subscription

Hello everyone.

We've been using replication since a while now and I just discovered that some user are not getting all the data they should. Let me explain the way the whole thing is configured for us.

We have a few tables that are replicated using the Merge process with filtering on the data using SUSER_SNAME() function. My users are salesrep using Laptop and they are synchronizing on demand. The merge process is configured so that a row will go to only one subscription --> Each rep has their owns customers and related tables informations.

I noticed that some users do not have locally all data (from their customers) from some tables while they should. I didn't checked in details but it is possible that these users are new reps replacing other staff that left the company. Is it possible that the data they do not receive is marked as to be previous reps owner because the replication is set as "A row from this table will go to only one subscription" ? The new rep uses the same laptop, therefore the exactly same computer name, only the username changes.

Because of this, now I'm having a bunch of conflicts when replicating because we're generating some ID based on existing data when the user enter new records in some tables; since the data is not sitting locally for them, the ID generated is not the proper one and we now have two identical ID (one on the server and one locally) which cause the conflicts to occurs.

Any helps would be appreciated. Thanks.

BUMP!

sql