получение повторяющихся строк

Я не могу получить, почему я получаю повторные данные для запроса ниже. Хотя я использовал distinct.

Запрос, как показано ниже-пожалуйста, помогите :
Могут быть некоторые проблемы с соединениями

SELECT DISTINCT(UM.USERNAME)'USER_NAME'
        ,UM.FIRSTNAME + ' ' + UM.LASTNAME AS 'EMPLOYEE NAME'
        ,US.USER_NAME 'USER_ID'
        ,US.MS_RIT_REPORTING_GROUP_MST_KEY
        ,RG.REPORTING_GROUP_NAME
        ,US.MS_RIT_REGION_MST_KEY
        ,RM.REGION_NAME
        ,US.MS_RIT_SUB_REGION_MST_KEY
        ,SM.SUB_REGION_NAME
        ,TP.TASK_MST_KEY
        ,TTM.TASK_TYPE_NAME
        ,CT.*
        ,FM.TASK_STATUS
        ,FM.TASK_START_DATE
        ,FM.TASK_END_DATE
        ,SRM.ROLE_NAME AS ROLENAME
    FROM USERS_MASTER UM
    INNER JOIN MS_RIT_USER_SKILLSET_MAP US ON UM.USERID = US.USER_NAME
    INNER JOIN DIM_MS_RIT_REPORTING_GROUP_MST RG ON US.MS_RIT_REPORTING_GROUP_MST_KEY = RG.MS_RIT_REPORTING_GROUP_MST_KEY
        AND UPPER(RG.ACTIVE) IN ('YES','1','Y')
    INNER JOIN DIM_MS_RIT_REGION_MST RM ON US.MS_RIT_REGION_MST_KEY = RM.MS_RIT_REGION_MST_KEY
        AND UPPER(RM.ACTIVE) IN ('YES','1','Y')
    INNER JOIN DIM_MS_RIT_SUB_REGION_MST SM ON US.MS_RIT_SUB_REGION_MST_KEY = SM.MS_RIT_SUB_REGION_MST_KEY
        AND UPPER(SM.ACTIVE) IN ('YES','1','Y')
    INNER JOIN MS_RIT_USER_TASK_MAP TP ON CONVERT(VARCHAR,UM.USERID) = TP.USER_ID
    INNER JOIN MS_RIT_CREATE_TASK CT ON CT.TASK_ID = TP.TASK_MST_KEY
    INNER JOIN WF_FRM_28_MST FM ON FM.TASK_ID = CT.TASK_ID
    INNER JOIN SEC_USER_ROLE SR ON SR.USER_ID = UM.USERNAME
    INNER JOIN SEC_ROLE_MST SRM ON SRM.ROLE_CODE = SR.ROLE_CODE
    INNER JOIN MS_RIT_TASK_TYPE_MASTER TTM ON CT.TASK_TYPE = TTM.TASK_TYPE_ID

1 ответ

  1. Используйте UNION, чтобы избавиться от дубликатов

    Одним из решений для проверки наличия дубликатов является объединение с помощью одного и того же запроса, если строки не были удалены, то они не являются дубликатами.

    (SELECT DISTINCT(UM.USERNAME)'USER_NAME'
            ,UM.FIRSTNAME + ' ' + UM.LASTNAME AS 'EMPLOYEE NAME'
            ,US.USER_NAME 'USER_ID'
            ,US.MS_RIT_REPORTING_GROUP_MST_KEY
            ,RG.REPORTING_GROUP_NAME
            ,US.MS_RIT_REGION_MST_KEY
            ,RM.REGION_NAME
            ,US.MS_RIT_SUB_REGION_MST_KEY
            ,SM.SUB_REGION_NAME
            ,TP.TASK_MST_KEY
            ,TTM.TASK_TYPE_NAME
            ,CT.*
            ,FM.TASK_STATUS
            ,FM.TASK_START_DATE
            ,FM.TASK_END_DATE
            ,SRM.ROLE_NAME AS ROLENAME
        FROM USERS_MASTER UM
        INNER JOIN MS_RIT_USER_SKILLSET_MAP US ON UM.USERID = US.USER_NAME
        INNER JOIN DIM_MS_RIT_REPORTING_GROUP_MST RG ON US.MS_RIT_REPORTING_GROUP_MST_KEY = RG.MS_RIT_REPORTING_GROUP_MST_KEY
            AND UPPER(RG.ACTIVE) IN ('YES','1','Y')
        INNER JOIN DIM_MS_RIT_REGION_MST RM ON US.MS_RIT_REGION_MST_KEY = RM.MS_RIT_REGION_MST_KEY
            AND UPPER(RM.ACTIVE) IN ('YES','1','Y')
        INNER JOIN DIM_MS_RIT_SUB_REGION_MST SM ON US.MS_RIT_SUB_REGION_MST_KEY = SM.MS_RIT_SUB_REGION_MST_KEY
            AND UPPER(SM.ACTIVE) IN ('YES','1','Y')
        INNER JOIN MS_RIT_USER_TASK_MAP TP ON CONVERT(VARCHAR,UM.USERID) = TP.USER_ID
        INNER JOIN MS_RIT_CREATE_TASK CT ON CT.TASK_ID = TP.TASK_MST_KEY
        INNER JOIN WF_FRM_28_MST FM ON FM.TASK_ID = CT.TASK_ID
        INNER JOIN SEC_USER_ROLE SR ON SR.USER_ID = UM.USERNAME
        INNER JOIN SEC_ROLE_MST SRM ON SRM.ROLE_CODE = SR.ROLE_CODE
        INNER JOIN MS_RIT_TASK_TYPE_MASTER TTM ON CT.TASK_TYPE = TTM.TASK_TYPE_ID)
    
        UNION
    
    (SELECT DISTINCT(UM.USERNAME)'USER_NAME'
            ,UM.FIRSTNAME + ' ' + UM.LASTNAME AS 'EMPLOYEE NAME'
            ,US.USER_NAME 'USER_ID'
            ,US.MS_RIT_REPORTING_GROUP_MST_KEY
            ,RG.REPORTING_GROUP_NAME
            ,US.MS_RIT_REGION_MST_KEY
            ,RM.REGION_NAME
            ,US.MS_RIT_SUB_REGION_MST_KEY
            ,SM.SUB_REGION_NAME
            ,TP.TASK_MST_KEY
            ,TTM.TASK_TYPE_NAME
            ,CT.*
            ,FM.TASK_STATUS
            ,FM.TASK_START_DATE
            ,FM.TASK_END_DATE
            ,SRM.ROLE_NAME AS ROLENAME
        FROM USERS_MASTER UM
        INNER JOIN MS_RIT_USER_SKILLSET_MAP US ON UM.USERID = US.USER_NAME
        INNER JOIN DIM_MS_RIT_REPORTING_GROUP_MST RG ON US.MS_RIT_REPORTING_GROUP_MST_KEY = RG.MS_RIT_REPORTING_GROUP_MST_KEY
            AND UPPER(RG.ACTIVE) IN ('YES','1','Y')
        INNER JOIN DIM_MS_RIT_REGION_MST RM ON US.MS_RIT_REGION_MST_KEY = RM.MS_RIT_REGION_MST_KEY
            AND UPPER(RM.ACTIVE) IN ('YES','1','Y')
        INNER JOIN DIM_MS_RIT_SUB_REGION_MST SM ON US.MS_RIT_SUB_REGION_MST_KEY = SM.MS_RIT_SUB_REGION_MST_KEY
            AND UPPER(SM.ACTIVE) IN ('YES','1','Y')
        INNER JOIN MS_RIT_USER_TASK_MAP TP ON CONVERT(VARCHAR,UM.USERID) = TP.USER_ID
        INNER JOIN MS_RIT_CREATE_TASK CT ON CT.TASK_ID = TP.TASK_MST_KEY
        INNER JOIN WF_FRM_28_MST FM ON FM.TASK_ID = CT.TASK_ID
        INNER JOIN SEC_USER_ROLE SR ON SR.USER_ID = UM.USERNAME
        INNER JOIN SEC_ROLE_MST SRM ON SRM.ROLE_CODE = SR.ROLE_CODE
        INNER JOIN MS_RIT_TASK_TYPE_MASTER TTM ON CT.TASK_TYPE = TTM.TASK_TYPE_ID)