Useful SQL Queries regarding Contact information and E-mail addresses within GoldMine

Version 1

    Details

    Useful SQL Queries regarding Contact information and E-mail addresses within GoldMine

    GoldMine Technical Support does not assist in creating (complex) SQL queries, but the below queries were often requested by customers and also are often used by GoldMine Technical Support during investigations

    Please keep in mind that all queries are always only provided as-is and you may want or need to adjust additional information you may want to see or not see.

    All SQL queries are SELECT statements and will not change any data within the GoldMine database and they can either ve performed from within GoldMine (*) or from the SQL Server Management Studio

    (*) the GoldMine SQL Query feature does not respect Record ownership and curtaining

    A. Via GoldMine's SQL query feature

    1. Tools >> SQL Query
    2. Copy and paste the desired SELECT query from below into the Query pane
    3. Button Query
    >> the result set will appear in the lower pane
    4. the query can be saved via the Save button for a later use

    B. via SQL Server Management Studio
    1. Open the SQL Server Management Studio
    2. Button New Query
    3. Make sure to select the (desired) GoldMine database from the database drop down
    4.
    Copy and paste the desired SELECT query from below into the Query pane
    5. run the query via the button Execute (red exclamation mark)
    >> the result set will appear in the lower pane


    Resolution


    (1) Primary contacts and their primary e-mail addresses

    SELECT DISTINCT C1.COMPANY,  C1.CONTACT, C1.ADDRESS1, C1.ADDRESS2,
    C1.CITY, C1.STATE, C1.ZIP, ISNULL(CS.CONTSUPREF,'')+ISNULL(CS.ADDRESS1,'') AS [E-MAIL ADDRESS], CS.MERGECODES
    FROM CONTSUPP AS CS,  CONTACT1 AS C1
    WHERE C1.ACCOUNTNO=CS.ACCOUNTNO AND
     (CS.RECTYPE = 'P' AND CS.CONTACT = 'E-MAIL ADDRESS'
     AND CS.ZIP LIKE '_1%')
     
     
    (2) Contact records that do not have any E-mail address


    SELECT CONTACT1.ACCOUNTNO, CONTACT1.COMPANY, CONTACT1.CONTACT
    FROM CONTACT1 WHERE CONTACT1.ACCOUNTNO NOT IN
    (SELECT CONTSUPP.ACCOUNTNO FROM CONTSUPP WHERE CONTSUPP.CONTACT = 'E-MAIL ADDRESS')
    ORDER BY CONTACT1.COMPANY

    (3) Contact records that do not have a Primary E-mail Address

    SELECT CONTACT1.ACCOUNTNO, CONTACT1.COMPANY, CONTACT1.CONTACT
    FROM CONTACT1 WHERE CONTACT1.ACCOUNTNO NOT IN
    (SELECT ACCOUNTNO FROM CONTSUPP where (CONTACT = 'E-MAIL ADDRESS' AND ZIP LIKE '_1%'))
    ORDER BY CONTACT1.COMPANY

    (4) Contact records with other email address


    SELECT CONTACT1.ACCOUNTNO, CONTACT1.COMPANY, CONTACT1.CONTACT
    FROM CONTACT1 WHERE CONTACT1.ACCOUNTNO IN
    (SELECT ACCOUNTNO FROM CONTSUPP where (CONTACT = 'E-MAIL ADDRESS' AND SUBSTRING(ZIP,2,1)= '0'))

    (5) Basic check for Invalid E-mail address
    (Does not contain the @ sign and . for domain)


    SELECT CONTACT1.CONTACT, CONTACT1.COMPANY, ISNULL(CONTSUPP.CONTSUPREF,'')+ISNULL(CONTSUPP.ADDRESS1,'') AS EMAIL
    FROM CONTACT1,CONTSUPP
    WHERE  CONTACT1.ACCOUNTNO=CONTSUPP.ACCOUNTNO AND CONTSUPP.U_CONTACT LIKE 'E-MAIL ADDRESS%'
    AND ISNULL(CONTSUPP.CONTSUPREF,'')+ISNULL(CONTSUPP.ADDRESS1,'') NOT LIKE '%@%.%'
    ORDER BY CONTACT1.COMPANY

    (6) Additional contacts and their e-mail addresses


    SELECT DISTINCT
    C1.COMPANY, CS1.CONTACT, CS1.ADDRESS1, CS1.ADDRESS2, CS1.CITY, CS1.STATE,CS1.ZIP, ISNULL(CS2.CONTSUPREF,'')+ISNULL(CS2.ADDRESS1,'') AS [E-MAIL ADDRESS], CS2.MERGECODES
    FROM  CONTSUPP AS CS1, CONTSUPP AS CS2, CONTACT1 AS C1
    WHERE  C1.ACCOUNTNO=CS1.ACCOUNTNO AND
    CS1.ACCOUNTNO = CS2.ACCOUNTNO AND CS1.RECTYPE = 'C' AND
    (CS2.RECTYPE = 'P' AND CS2.CONTACT = 'E-MAIL ADDRESS'
    AND CS2.LINKACCT = CS1.RECID)

    (7) Duplicate E-mail addresses

    SELECT CONTACT1.ACCOUNTNO, CONTACT1.COMPANY, ISNULL(CONTSUPP.CONTSUPREF,'')+ISNULL(CONTSUPP.ADDRESS1,'') AS [E-MAIL ADDRESS]
    FROM CONTACT1,CONTSUPP
    WHERE
    CONTACT1.ACCOUNTNO=CONTSUPP.ACCOUNTNO AND ISNULL(CONTSUPP.CONTSUPREF,'')+ISNULL(CONTSUPP.ADDRESS1,'') IN
    (SELECT ISNULL(CONTSUPP.CONTSUPREF,'')+ISNULL(CONTSUPP.ADDRESS1,'') FROM CONTSUPP,CONTACT1 WHERE RECTYPE='P' AND CONTSUPP.CONTACT='E-MAIL ADDRESS'
    AND CONTSUPP.ACCOUNTNO=CONTACT1.ACCOUNTNO
    GROUP BY ISNULL(CONTSUPP.CONTSUPREF,'')+ISNULL(CONTSUPP.ADDRESS1,'') HAVING COUNT(*)>1)
    ORDER BY ISNULL(CONTSUPP.CONTSUPREF,'')+ISNULL(CONTSUPP.ADDRESS1,'')


    (8) Complete List of E-mail addresses with extra Information

    SELECT CONTACT1.COMPANY, CONTACT1.CONTACT, CONTACT1.TITLE, CONTACT1.PHONE1,ISNULL(CONTSUPP.CONTSUPREF, '') + ISNULL(CONTSUPP.ADDRESS1, '') AS EMAIL, CONTACT1.ACCOUNTNO,'Primary' AS SORTFLAG
    FROM   CONTSUPP INNER JOIN
           CONTACT1 ON CONTSUPP.ACCOUNTNO = CONTACT1.ACCOUNTNO
    WHERE  (CONTSUPP.CONTACT = 'E-mail Address') AND (CONTSUPP.RECTYPE = 'P') AND (CONTSUPP.ZIP LIKE '_1%')
    UNION
    SELECT CONTACT1.COMPANY, CONTSUPP.CONTACT, CONTSUPP.title, CONTSUPP.phone,
        ISNULL(CONTSUPPEMAIL.CONTSUPREF, '') + ISNULL(CONTSUPPEMAIL.ADDRESS1, '') AS EMAIL, CONTACT1.ACCOUNTNO,'Additional' AS SORTFLAG
    FROM CONTSUPP INNER JOIN
          CONTACT1 ON CONTSUPP.ACCOUNTNO = CONTACT1.ACCOUNTNO INNER JOIN
          CONTSUPP CONTSUPPEMAIL ON CONTSUPP.RECID = CONTSUPPEMAIL.LINKACCT
    WHERE (CONTSUPP.RECTYPE = 'C') AND (CONTSUPPEMAIL.RECTYPE = 'P') AND (CONTSUPPEMAIL.CONTACT = 'E-mail Address')
    UNION
    SELECT CONTACT1.COMPANY, CONTACT1.CONTACT, CONTACT1.title, CONTACT1.phone1,ISNULL(CONTSUPP.CONTSUPREF, '') + ISNULL(CONTSUPP.ADDRESS1, '') AS EMAIL, CONTACT1.ACCOUNTNO,'Other' AS SORTFLAG
    FROM   CONTSUPP INNER JOIN
           CONTACT1 ON CONTSUPP.ACCOUNTNO = CONTACT1.ACCOUNTNO
    WHERE  (CONTSUPP.CONTACT = 'E-mail Address') AND (CONTSUPP.RECTYPE = 'P') AND (CONTSUPP.ZIP LIKE '_0%') AND ISNULL(CONTSUPP.LINKACCT,'') = ''
    ORDER BY CONTACT1.COMPANY, CONTACT1.ACCOUNTNO, SORTFLAG