SQL Learning Center - GoldMine

Use SQL queries to get answers from GoldMine

August 5, 2009

More Than Two Matching Company Records Not In OrgChart

by Doug Steinschneider @ 6:46 pm. Filed under MSSQL Contacts

Type: MSSQL
Description: More Than Two Matching Company Records Not In OrgChart - We use GoldBox to add contact records automatically to company name based orgcharts. If users enter a record with no value in contact it doesn’t add the record. We use this to find any records where there are two matching company names but they have not been put together into an OrgChart based on company name.

  1.  
  2. SELECT
  3.  c1.company
  4. ,c1.contact
  5. ,c1.accountno
  6. FROM
  7.  contact1 c1
  8. WHERE
  9.  c1.company > ''
  10.  AND c1.company IN
  11.   (SELECT
  12.     company
  13.    FROM
  14.     contact1
  15.    WHERE
  16.     company > ''
  17.    GROUP BY
  18.     company
  19.    HAVING
  20.     COUNT(company) > 1)
  21.   AND c1.accountno NOT IN
  22.   (SELECT
  23.     accountno
  24.    FROM
  25.     contsupp
  26.    WHERE
  27.     rectype = 'O')
  28.        

August 13, 2008

Find Main record for an additional contact

by Doug Steinschneider @ 7:46 pm. Filed under MSSQL Contacts

Type: MSSQL
Description: Find Main record for an additional contact

  1.  
  2. SELECT
  3.  c1.accountno
  4. ,c1.company
  5. ,c1.contact
  6. ,c1.key5
  7. FROM
  8.  contact1 c1
  9.  JOIN contsupp cs
  10.   ON c1.accountno=cs.accountno
  11. WHERE
  12.  cs.contact LIKE '%John Smith%'
  13.        

May 28, 2008

Find Deleted Contacts by Comparing Current Database with Older Version

by Doug Steinschneider @ 9:53 pm. Filed under MSSQL Query Analyzer, MSSQL Contacts

Type: MSSQL
Description: In this more advanced query we see the following scenario: You suspect that some contacts have been deleted from GoldMine when instead they should have been moved to your Archive Database. We move contacts flagged for deletion to a database called GoldMine_Archive. In this query we check for GoldMine accountnos in an older backup that weve restored to an alternate MSSQL database GoldMine_20070806 against all the accountnos in both the current GoldMine and GoldMine_Archive.

  1.  
  2. SELECT
  3.  c1r.contact
  4. ,c1r.company
  5. ,c1r.city
  6. ,c1r.STATE
  7. ,c1r.key1
  8. ,c1r.key2
  9. ,c1r.key3
  10. ,c1r.key4
  11. ,c1r.key5
  12. ,c1r.accountno
  13. FROM
  14.  GoldMine_20070806.dbo.CONTACT1 c1r
  15. WHERE
  16.  c1r.accountno NOT IN
  17.  (SELECT accountno FROM contact1 UNION SELECT accountno FROM GoldMine_Archive.dbo.CONTACT1)
  18. ORDER BY
  19.  c1r.company
  20. ,c1r.contact
  21.  

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.  

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.114 seconds