Multiple Joins - Need Help

have the following code for ONE Inner Join, but I want to add another join for another Table and Fields... can you help me with the syntax:

SELECT DISTINCT

CTR.ReqID, CTR.SpecimenID, CTR.LabID, CTR.ProcedureID, CTR.TestID, CTR.Isolate, CTR.Problem,

CTR.ProblemComments, CP.ProcedureID, CP.Description AS CPProcedureDescription

FROM ClinicalTestsRequested CTR inner join ClinicalProcedures CP

ON CTR.ProcedureID=CP.ProcedureID

WHERE (CTR.SpecimenID = @.Accession)

I want to add another Join to the above where:

Table = ClinicalTests CT
Fields = CT.TestID, CT.Description AS CTTestDescription

and Compare = CTR.TestID to CT.TestID

Thanks !!

after your on clause for the first inner join add:

inner join clinicalTests ct on ctr.testid = ct.testid

add your columns to the list in the select.

Better yet, use sql server management studio express to design the query for you (it doensn't matter if your database is sql2005 or 2000). Create a new query for one of your tables and then use the query designer. This will allow you to drag and drop joins, make them into outer joins, add columns, etc, using a design tool.

--JJ