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

Use 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


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

Thanks Tom, i'm pretty new to SQL so not very familiar with the syntax
yet. Glad this one was an easy fix!

Time Difference Calculations
Customers & Phones
Return only the Third Row
Choosing one record over another
can't return zero when no rows exist using aggregate function sum