Estoy tratando de encontrar proveedores duplicados de una base de datos usando varios campos devendedor mesa yvendedor_direccion mesa. La cuestión es que cuanto más unida interna hago, menos la consulta está perdiendo resultados potenciales. Si bien no tengo duplicados en la identificación del proveedor, estoy buscando encontrar uno potencial que sea similar.

Aquí está mi consulta hasta ahora:

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';