SQL Query Samples

Count Number of Records

To count the number of Contact Records, write your statement as:

SELECT count (*) FROM Contact1

For count statements, notice the wildcard (*) is surrounded by parentheses.

Fields in Each Table

To return a listing of all fields in each table for GoldMine, write a query such as:

SELECT * FROM Contact1

and then change the table name for each listing required:

SELECT * FROM CAL, select * from ContHist

Restrict to City

To find all contacts in New York City, write your statement as:

SELECT * FROM Contact1 WHERE city = “New York”

A query to find all New York City records where users entered both initial cap and lowercase:

SELECT * FROM Contact1 WHERE city like “_ew _ork”

Use the underscore _ character to match any single character.

Restrict to Two States

To find all contacts in CA and NY, write your statement as:

SELECT * FROM Contact1 WHERE state = “NY” OR state = “CA” ORDER BY state, city

Duplicate Contacts

Finding all possible duplicates in a database can be difficult. A query to find duplicate Contact Records in the database is to search on duplicate Account Numbers looking in both the Contact1 and Contact2 tables:

SELECT count(*), AccountNo The Account Number field uniquely identifies a contact record and is the common field value that links the Contact1 table to Contact2, Cal, ContSupp, ContHist, and OpMgr. FROM Contact2 GROUP BY AccountNo HAVING Count(*) > 1

Duplicate RecID

To find Contact Records with duplicate RecIDs, use this query:

SELECT recid FROM Contact2 GROUP BY recid HAVING count(recid) > 1

Find Orphans

An orphan record exists when there is a Contact2 record but NOT a Contact1 record.

SELECT * FROM Contact2 WHERE AccountNo NOT IN (SELECT AccountNo from Contact1)

Contact Records with No E-mail Address

To subset Contact Records with no e-mail address, use this query:

SELECT company, contact FROM Contact1 WHERE accountno NOT IN (SELECT accountno FROM ContSupp WHERE contact = “E-mail Address”)