SQL Learning Center - GoldMine

Use SQL queries to get answers from GoldMine

August 5, 2009

Additional Contact Email

by Doug Steinschneider @ 4:35 pm. Filed under MSSQL Email

Type: MSSQL
Description: This uses the ISNULL function to correctly bring back emails that fit inside contsupp.contsupref and the longer ones that spill over in to contsupp.address1

  1.  
  2. SELECT
  3.  c1.key1
  4. ,cs.contact
  5. ,ISNULL(cs1.contsupref,'') + ISNULL(cs1.address1,'') AS email
  6. FROM
  7.  contact1 c1
  8.  JOIN contsupp cs
  9.   ON c1.accountno=cs.accountno
  10.   JOIN contsupp cs1 ON cs.recid=cs1.linkacct
  11. WHERE
  12.  cs.rectype='C'
  13.  AND cs1.contact='E-Mail Address'
  14.  AND cs1.rectype = 'P'  
  15.        

April 27, 2009

Find Primary Email Addresses for State and Contact Type LIKE

by Doug Steinschneider @ 11:23 am. Filed under MSSQL Email

Type: MSSQL
Description: Find Primary Email Addresses for State and Contact Type LIKE (type in whole or partial words between the percent signs)

  1.  
  2. SELECT
  3.  c1.contact
  4. ,cs.contsupref
  5. ,cs.accountno
  6. ,c1.lastname
  7. ,c1.key1 AS ContType
  8. FROM
  9.  contact1 c1
  10.  JOIN contsupp cs
  11.   ON c1.accountno=cs.accountno
  12. WHERE
  13.  cs.contact='E-mail Address'
  14.  AND SUBSTRING(cs.zip FROM 2 FOR 1) = '1'
  15.  AND c1.STATE IN ('MA')
  16.  AND (c1.key1 LIKE '%Instr%'
  17.       OR c1.key1 LIKE '%Stu%')
  18. ORDER BY
  19.  c1.lastname
  20.  

October 28, 2008

Find Malformed Email Addresses

by Doug Steinschneider @ 4:31 pm. Filed under MSSQL Email

Type: MSSQL
Description: Find Malformed Email Addresses
NOTE: Because this query is looking for special characters it doesn’t display properly in a web page - please cut and paste the text of the query from here: Find Malformed Email Addresses


SELECT
accountno
,cs.contsupref AS 'E-Mail Address'
FROM
contsupp cs
WHERE
(cs.CONTACT = 'E-mail Address')
AND (SELECT
CASE
WHEN cs.contsupref IS NULL
OR CHARINDEX('@.',cs.contsupref) > 0
OR CHARINDEX('.@',cs.contsupref) > 0
OR CHARINDEX('..',cs.contsupref) > 0
OR CHARINDEX('"', cs.contsupref) <> 0
OR CHARINDEX(’(', cs.contsupref) <> 0
OR CHARINDEX(’)', cs.contsupref) <> 0
OR CHARINDEX(’,', cs.contsupref) <> 0
OR CHARINDEX(’< ', cs.contsupref) <> 0
OR CHARINDEX(’>', cs.contsupref) <> 0
OR CHARINDEX(’;', cs.contsupref) <> 0
OR CHARINDEX(’:', cs.contsupref) <> 0
OR CHARINDEX(’[', cs.contsupref) <> 0
OR CHARINDEX(’]', cs.contsupref) <> 0
OR RIGHT(RTRIM(cs.contsupref),1) = ‘.’
OR CHARINDEX(’ ‘,LTRIM(RTRIM(cs.contsupref))) > 0
OR LEN(cs.contsupref)-1 < = CHARINDEX('.', cs.contsupref)
OR cs.contsupref LIKE '%@%@%'
OR cs.contsupref NOT LIKE '%@%.%' THEN 0
ELSE 1
END) = 0

September 11, 2008

Find all contacts sent a certain attachment

by Doug Steinschneider @ 9:11 pm. Filed under MSSQL History, MSSQL Email

Type: MSSQL
Description: Find all contacts sent a certain attachment

  1.  
  2. SELECT
  3.  c1.contact
  4. ,c1.company
  5. ,ch.rectype
  6. ,mb.maildate
  7. ,c1.accountno
  8. FROM
  9.  contact1 c1
  10.  JOIN conthist ch
  11.  ON c1.accountno=ch.accountno
  12.  JOIN mailbox mb
  13.   ON ch.recid = mb.linkrecid
  14. WHERE
  15.  mb.maildate > '11/01/2007'
  16.  AND mb.rfc822 LIKE '%Put attachment filename here%'
  17.  

May 28, 2008

by Doug Steinschneider @ 9:39 pm. Filed under MSSQL Email

Type: MSSQL
Description: This query returns email records for a contact that have keywords in the body of the email. In the example below we are looking for emails that contain the words “Subscription”, “Offering”, “Document ” or “Documents “. Notice the trailing space after the document and documents examples.

  1.  
  2. SELECT
  3.  c1.contact
  4. ,c1.company
  5. ,mb.maildate
  6. ,mb.mailref
  7. ,c1.accountno
  8. FROM
  9.  contact1 c1
  10.   JOIN conthist ch (NOLOCK)
  11.    ON c1.accountno=ch.accountno
  12.     JOIN mailbox mb
  13.      ON ch.recid = mb.linkrecid
  14. WHERE
  15.  mb.rfc822 LIKE '%type keywords between percent signs like examples below%'
  16.  AND (mb.rfc822 LIKE '%Subscription%'
  17.   OR mb.rfc822 LIKE '%Offering%')
  18.  AND (mb.rfc822 LIKE '%Document %'
  19.   OR mb.rfc822 LIKE '%Documents %')
  20.  AND mb.folder = 'Filed'
  21. GROUP BY
  22.  c1.contact
  23. ,c1.company
  24. ,mb.maildate
  25. ,mb.mailref
  26. ,c1.accountno
  27.  

May 25, 2008

Contact Email with Specific Mergecode for Primary and Additional Contacts

by Doug Steinschneider @ 10:43 am. Filed under MSSQL Email, MSSQL Contacts

Categories: Contact, Email

Description: This returns all emails for a mergecode specified in the WHERE portion of the query between the percent signs for both primary and additional contact email addresses.

  1.  
  2. SELECT
  3.  c1.company
  4. ,cs.contact
  5. ,c1.address1
  6. ,c1.address2
  7. ,c1.city
  8. ,c1.STATE
  9. ,c1.zip
  10. ,c1.country
  11. ,cs1.contsupref AS Email
  12. ,cs1.mergecodes
  13. ,c1.accountno
  14. FROM
  15.  contsupp cs (NOLOCK)
  16.   LEFT OUTER JOIN contsupp cs1
  17.    ON cs.recid = cs1.linkacct
  18.     LEFT OUTER JOIN contact1 c1
  19.      ON cs.accountno = c1.accountno
  20. WHERE
  21.  cs.rectype = 'C'
  22.  AND cs1.rectype = 'P'
  23.  AND cs1.contact = 'E-mail Address'
  24.  AND cs1.mergecodes LIKE '%Put mergecode here between percent signs%'
  25. UNION
  26. SELECT
  27.  c1.company
  28. ,c1.contact
  29. ,c1.address1
  30. ,c1.address2
  31. ,c1.city
  32. ,c1.STATE
  33. ,c1.zip
  34. ,c1.country
  35. ,cs.contsupref AS AddContEmail
  36. ,cs.mergecodes AS AddlMergeCode
  37. ,c1.accountno
  38. FROM
  39.  contact1 c1 (NOLOCK)
  40.   JOIN contsupp cs
  41.    ON c1.accountno=cs.accountno
  42. WHERE
  43.  cs.mergecodes LIKE '%Put mergecode here between percent signs%'
  44.  AND cs.rectype = 'P'
  45.  AND cs.contact = 'E-mail Address'
  46.  AND cs.zip LIKE '_1__'
  47.  

Contact Email with Specific Mergecode for Primary Contacts Only

by Doug Steinschneider @ 9:46 am. Filed under MSSQL Email


Type: MS SQL
Categories: Contact, Email

Description: This returns contact information along with the primary email address for those contacts that have a value in the email mergecode which is specified in the WHERE portion of the query at cs.mergecodes LIKE ‘%Put mergecode between these percent signs%’. The % sign allows you to search of that value anywhere in the mergecode field.

  1.  
  2. SELECT
  3.  c1.company
  4. ,c1.contact
  5. ,c1.address1
  6. ,c1.address2
  7. ,c1.city
  8. ,c1.STATE
  9. ,c1.zip
  10. ,c1.country
  11. ,c1.phone1
  12. ,cs.contsupref AS EmailAddress
  13. ,cs.mergecodes
  14. ,c1.key4 AS Rep
  15. ,c1.accountno
  16. FROM
  17.  contact1 c1 (NOLOCK)
  18.   JOIN contsupp cs
  19.    ON c1.accountno=cs.accountno
  20. WHERE
  21.  cs.mergecodes LIKE '%Put MergeCode value between these percent signs%'
  22.  AND cs.rectype = 'P'
  23.  AND cs.contact = 'E-mail Address'
  24.  AND cs.zip LIKE '_1__'
  25.        

May 22, 2008

Main Contact, Userdefined Field and Email Address

by Doug Steinschneider @ 1:42 pm. Filed under MSSQL Email

Description: This query returns main contact, userdefined field and primary email address from contsupp for key1 (Contact Type) equals ‘Customer’

  1.  
  2. SELECT
  3.   c1.contact
  4. , c1.company
  5. , c1.key1
  6. , c1.key5
  7. , c2.userdef01
  8. , ISNULL(cs.contsupref,'') + ISNULL(cs.address1,'') AS email
  9. FROM
  10.  contact1 (NOLOCK)
  11.   JOIN  contact2
  12.   ON c1.accountno=c2.accountno
  13.    JOIN  contsupp cs
  14.    ON c1.accountno=cs.accountno
  15. WHERE
  16.  c1.key1 = 'Customer'
  17.  AND cs.contact = 'E-mail Address'
  18.  AND cs.zip LIKE '_1__'
  19.  

GoldMine SQL Tutorial:

General SQL Tutorials:

SQL Help Links:

Recommended Reading - SQL Books:

RSS:

Links:

Search For SQL Query:

GoldMine MS SQL Queries:

GoldMine dBase SQL Queries (LocalSQL):

21 queries. 0.426 seconds