Everyday SQL: SELECT

SQL is both the underlying database (Microsoft SQL Server) of all Microsoft Dynamics products and it’s a language (SQL = Structured Query Language) which can be used to query and update the data in the database. Below is some basic SQL code that can be very beneficial in day to day use and maintenance of the Microsoft Dynamics. A word of caution: You can also do great harm with SQL! Use extreme caution when using any kind of statement that changes the database, test everything out on a test copy of the database, and always have a recent backup. Ask your administrator for help in accessing SQL. They can also limit your rights to the database to allow you to view the data only without being able to change it or delete it.

SELECT: A Select statement will allow you to query data in a table. It is view only and can not change your data. You can use a select along or with the Where clause to specify parameters. For example:

Select * from customers where balance > 1000

This statement selects all fields (the * is a wildcard that means all fields) from a database table named Customers where the field called Balance has a value greater than 1000. So in other words, display a list of all customers whose outstanding balance is greater than $1000. Keep in mind, you can specify any table name in place of Customers and any field name in place of Balance.

If you want to sort the data, add the Order By clause:

Select * from customers where balance > 1000 order by balance desc

This will sort the results by the Balance field in descending order (desc). You can specify ascending order (asc) instead, although if you leave this qualifier off completely, ascending order will be assumed.

You can also just see the output for specific fields or columns by listing them after the Select keyword:

Select custid, name, balance from customers where balance > 1000

This statement will return just 3 fields or columns in your results: custid, name and balance.

Here’s a useful statement, find records in one table based on whether or not they are present in another table.

Select * from customers where custid in (select custid from ardoc)

This statement will return all records in the Customers table where the custid is also present in the Ardoc table. So this query would show you all Customers who had ever had an invoice entered in the system. You can change it to show you where the records don’t exist by inserting the keyword Not before In:

Select * from customers where custid not in (select custid from ardoc)

This would be helpful is showing you customers records that had never been used and might therefore be able to be purged from your database.

Sometimes just get a count or number of records is useful. The Count function can handle that. For example:

Select count(*) from customers where custid not in (select custid from ardoc)

This will return just the number of records. You can use the all fields wildcard (*) or specify any one field name inside the parenthesis.

Finally, you can query more than one table at a time using the Join function. For example:

Select * from customer join ardoc on customer.custid = ardoc.custid

The first part of the statement, select * from customer is the same as a standard select statement. Follow this with the keyword Join and then specify the table you wish to join to, in this case ardoc. You must have a common field to join two tables and in this example it is the custid field. The last part of the statement specifies this relationship.

You can then combine the join with other elements of a select statement. For example, we can combine the count function and a where clause:

select count(*) from customer join ardoc on customer.custid = ardoc.custid where customer.state = 'NY'

Keep in mind that when working with multiple tables in a Join, you must specify the table name with each field name, as in above, customer.state (state field in the customer table) as opposed to ardoc.state (state field in the ardoc table).

There are hundreds if not thousands of potential uses for the Select statement.  You can’t do any harm running select, so jump in and try out some different combinations. Practice is the best teacher.

To find what tables and fields to query, you need a database schema (a listing of what each table and field is used for). Depending on both your edition and version of Dynamics, the schema may or may not be readily accessible. Ask your database administrator or reseller for assistance with finding the schema for your specific edition and version.