Quality Essentials Suite

SELECT   HQMS_Test.TestID,  HQMS_Test.TestCode, HQMS_Criteria.CriteriaID, 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, 
                         SysCodeType_Criteria.SysCodeDesc AS CriteriaSysCodeType, SysCodeType_Test.SysCodeDesc AS Test_SysCodeType, UofM_Criteria.SysCodeDesc AS CriteriaUofM, UofM_Test.SysCodeDesc AS Test_UnitOfMeasure, 
                         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_CriteriaTest INNER JOIN
                         HQMS_Test ON HQMS_CriteriaTest.TestID = HQMS_Test.TestID INNER JOIN
                         HQMS_Criteria ON HQMS_CriteriaTest.CriteriaID = HQMS_Criteria.CriteriaID INNER JOIN
                         HQMS_SysCodes AS DataType_Criteria ON HQMS_Criteria.DataTypeSID = DataType_Criteria.SysCodesID INNER JOIN
                         HQMS_SysCodes AS DataType_Test ON HQMS_Test.DataTypeSID = DataType_Test.SysCodesID LEFT OUTER JOIN
                         HQMS_SysCodes AS SysCodeType_Test ON HQMS_Test.SysCodeTypeSID = SysCodeType_Test.SysCodesID LEFT OUTER JOIN
                         HQMS_SysCodes AS SysCodeType_Criteria ON HQMS_Criteria.SysCodeTypeSID = SysCodeType_Criteria.SysCodesID LEFT OUTER JOIN
                         HQMS_SysCodes AS UofM_Criteria ON HQMS_Criteria.UnitOfMeasureSID = UofM_Criteria.SysCodesID LEFT OUTER JOIN
                         HQMS_SysCodes AS UofM_Test ON HQMS_Test.UnitOfMeasureSID = UofM_Test.SysCodesID
             WHERE CASE WHEN SysCodeType_Test.SysCodeDesc <> SysCodeType_Criteria.SysCodeDesc THEN 'SysCodeType Issue' ELSE 'No Issues' END <> 'No Issues'