Estou tentando encontrar fornecedores duplicados de um banco de dados usando vários campos defornecedor mesa evendor_address mesa. O problema é que, quanto mais junção interna eu faço, menos a consulta perde resultados em potencial. Embora eu não tenha duplicado no ID do fornecedor, procure um potencial que seja semelhante.

Aqui está minha consulta até agora:

SELECT 
     o.vendor_id
    ,o.vndr_name_shrt_user
    ,O.COUNTRY 
    ,O.VENDOR_NAME_SHORT 
    ,B.POSTAL
    ,B.ADDRESS1
    ,SAME_ADDRESS_NB
    ,SAME_POSTAL_NB
    ,OC.SAME_SHORT_NAME
    ,oc.SAME_USER_NUM
FROM VENDOR o

JOIN vendor_addr B ON o.VENDOR_ID = B.VENDOR_ID

INNER JOIN (
    SELECT vndr_name_shrt_user, COUNT(*) AS SAME_USER_NUM
    FROM VENDOR 
    WHERE COUNTRY = 'CANADA'
    AND VENDOR_STATUS = 'A'
    GROUP BY vndr_name_shrt_user
    HAVING COUNT(*) > 1
) oc on o.vndr_name_shrt_user = oc.vndr_name_shrt_user

INNER JOIN ( SELECT VENDOR_NAME_SHORT, COUNT(*) AS SAME_SHORT_NAME
    FROM VENDOR 
    WHERE COUNTRY = 'CANADA'
    AND VENDOR_STATUS = 'A'
    GROUP BY VENDOR_NAME_SHORT
    HAVING COUNT(*) > 1
) oc on o.VENDOR_NAME_SHORT = oc.VENDOR_NAME_SHORT

INNER JOIN (SELECT POSTAL, COUNT(*) AS SAME_POSTAL_NB
    FROM vendor_addr 
    WHERE COUNTRY = 'CANADA'
    AND COUNTRY ='CANADA'
    AND POSTAL != ' '
    GROUP BY POSTAL
    HAVING COUNT(*) > 1
) oc on b.POSTAL = oc.POSTAL

INNER JOIN (SELECT ADDRESS1, COUNT(*) AS SAME_ADDRESS_NB
    FROM ps_vendor_addr 
    WHERE COUNTRY = 'CANADA'
    AND COUNTRY ='CANADA'
    AND ADDRESS1 != ' '
    GROUP BY ADDRESS1
    HAVING COUNT(*) > 1
) oc on b.ADDRESS1 = oc.ADDRESS1   
WHERE O.COUNTRY ='CANADA' 
    AND B.COUNTY = 'CANADA';