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.        

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.        

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