JOIN issue

Hello All... would love some help with the following SQL:

I am joining 3 Tables on Accession Number, to retrieve data. The Tables are:

ClinicalPatient
ClinicalSpecimen
ClinicalTestsRequested

There is (1) record in the ClinicalPatient Table for Accession 6281.
There are (3) records in the ClinicalSpecimen Table for Accession 6281. Each Accession has a SpecimenID (6281-01, 6281-02, 6281-03).
When a Test is Requested, a record is written to the ClinicalTestsRequested table and a "1" is posted to the record in the "AddTest" Field.

At this point there are (2) records in the ClinicalTestsRequested table that have the Value of "1" in the "AddTest" field.

When I run the following query, all (3) records display, but the third one ALSO has a "1" displayed in the AddTest Field and it should be blank?? What am I missing here?? ClinicalTestsRequested table is the only one with the AddTest field.

THANKS !!

SELECT DISTINCT ClinicalPatient.PatientID, ClinicalPatient.MedRecord, ClinicalPatient.LastName, ClinicalPatient.FirstName, ClinicalPatient.Address1, ClinicalPatient.Accession, ClinicalSpecimen.SpecimenID AS CSSpecimenID, ClinicalSpecimen.Accession AS CSAccession, ClinicalTestsRequested.AddTest

FROM ClinicalPatient INNER JOIN
ClinicalSpecimen ON ClinicalPatient.Accession = ClinicalSpecimen.Accession INNER JOIN
ClinicalTestsRequested ON ClinicalTestsRequested.Accession = ClinicalSpecimen.Accession

WHERE (ClinicalPatient.Accession = 6281)

Can you post the data? From your description you have:

ClinicalPatient table
6281

ClinicalSpeciman
6281 01
6281 02
6281 03

ClinicalTestREquested
6281 1
6281 1

This doesn't really make sense to me -- if you're doing an inner join on all 3 tables you should only get back 2 rows -- unless I'm missing something

|||

After some research and offering Lunch to a Colleague, We ended up trying and using LEFT OUTER JOIN to accomplish this requirement.

Thanks for all responses.

SelectCommand="SELECT DISTINCT
ISNULL(ClinicalTestsRequested.AddTest, 0) AS Expr1, ClinicalPatient.PatientID, ClinicalPatient.MedRecord, ClinicalPatient.LastName,
ClinicalPatient.FirstName, ClinicalPatient.Address1, ClinicalPatient.Accession, ClinicalSpecimen.SpecimenID,ClinicalSpecimen.Accession AS
CSAccession

FROM ClinicalSpecimen
LEFT OUTER JOIN
ClinicalPatient ON ClinicalPatient.Accession = ClinicalSpecimen.Accession

LEFT OUTER JOIN
ClinicalTestsRequested AS ClinicalTestsRequested ON ClinicalTestsRequested.SpecimenID = ClinicalSpecimen.SpecimenID

WHERE (ClinicalSpecimen.Accession = @.Accession)">

RESULTS:

0147SommaJohn62816281-0362811147SommaJohn62816281-0162811147SommaJohn62816281-026281