I have this query which displays all the SpecimenTypeNames for all our cases for a ComponentID.
SELECT APVX.DOS.SpecimenType.SpecimenTypeName
FROM APVX.ORDERS.ComponentOrder INNER JOIN
APVX.ORDERS.ApCase INNER JOIN
APVX.ORDERS.TestOrder ON APVX.ORDERS.ApCase.CaseID = APVX.ORDERS.TestOrder.CaseID INNER JOIN
APVX.ORDERS.TestOrderSpecimen ON APVX.ORDERS.TestOrder.TestOrderID = APVX.ORDERS.TestOrderSpecimen.TestOrderID INNER JOIN
APVX.ORDERS.Specimen ON APVX.ORDERS.TestOrderSpecimen.SpecimenID = APVX.ORDERS.Specimen.SpecimenID ON
APVX.ORDERS.ComponentOrder.TestOrderID = APVX.ORDERS.TestOrder.TestOrderID INNER JOIN
&
View Complete Post