This query shows the differences between the Criteria and Test for the CriteriaItem relationship.


SELECT        HQMS_CriteriaItems.ItemID,HQMS_Test.TestID, HQMS_Test.TestCode, HQMS_Criteria.CriteriaID, HQMS_Criteria.CriteriaCode, HQMS_Criteria.SysCodeTypeSID AS CriteriaSysCodeType, HQMS_Criteria.DataTypeSID AS CriteriaDataTypeNumber, HQMS_Test.DataTypeSID AS TestDataTypeNumber, 
                         HQMS_Test.SysCodeTypeSID AS TestSysCodeType, HQMS_Criteria.UnitOfMeasureSID AS CriteriaUofMNumber, HQMS_Test.UnitOfMeasureSID AS TestUofMNumber, UofM_Test.SysCodeDesc AS Test_UnitOfMeasure, 
                         UofM_Criteria.SysCodeDesc AS CriteriaUofM, SysCodeType_Criteria.SysCodeDesc AS CriteriaSysCodeType, SysCodeType_Test.SysCodeDesc AS Test_SysCodeType, DataType_Criteria.SysCodeDesc AS CriteriaDataType, 
                         DataType_Test.SysCodeDesc AS Test_DataType, CASE WHEN UofM_Test.SysCodeDesc <> UofM_Criteria.SysCodeDesc THEN 'UofM Issue' ELSE 'No Uom Issues' END AS UOM_Issues, 
                         CASE WHEN SysCodeType_Test.SysCodeDesc <> SysCodeType_Criteria.SysCodeDesc THEN 'SysCodeType Issue' ELSE 'No Issues' END AS SysCodeTypeIssues, 
                         CASE WHEN DataType_Test.SysCodeDesc <> DataType_Criteria.SysCodeDesc THEN 'Data Type Issue' ELSE 'No Issues' END AS DataType_Issues
FROM            HQMS_SysCodes AS UofM_Criteria RIGHT OUTER JOIN
                         HQMS_SysCodes AS SysCodeType_Criteria RIGHT OUTER JOIN
                         HQMS_SysCodes AS DataType_Criteria INNER JOIN
                         HQMS_Criteria ON DataType_Criteria.SysCodesID = HQMS_Criteria.DataTypeSID INNER JOIN
                         HQMS_SysCodes AS DataType_Test INNER JOIN
                         HQMS_Test ON DataType_Test.SysCodesID = HQMS_Test.DataTypeSID INNER JOIN
                         HQMS_CriteriaItems ON HQMS_Test.TestID = HQMS_CriteriaItems.TestID ON HQMS_Criteria.CriteriaID = HQMS_CriteriaItems.CriteriaID LEFT OUTER JOIN
                         HQMS_SysCodes AS SysCodeType_Test ON HQMS_Test.SysCodeTypeSID = SysCodeType_Test.SysCodesID LEFT OUTER JOIN
                         HQMS_SysCodes AS UofM_Test ON HQMS_Test.UnitOfMeasureSID = UofM_Test.SysCodesID ON SysCodeType_Criteria.SysCodesID = HQMS_Criteria.SysCodeTypeSID ON 
                         UofM_Criteria.SysCodesID = HQMS_Criteria.UnitOfMeasureSID
WHERE  CASE WHEN SysCodeType_Test.SysCodeDesc <> SysCodeType_Criteria.SysCodeDesc THEN 'SysCodeType Issue' ELSE 'No Issues' END <> 'No Issues'