People generally ask me about resources for learning SQL. SQL is a great language for any database administrator to learn, because it gives you full control of the data, without limiting you to an application’s graphic user interface (GUI). With SQL you can query for records, and add, edit or remove them. This post is not meant as a tutorial on the subject, because great resources, like SQLzoo or O’Reilly’s , already exist. Instead, in this series of posts, I want to highlight why the SELECT statement is the most important of these.

Let’s start with a simple SELECT statement:

/* Example 1: Basic Query */
SELECT * FROM TextEntries

This will return all the fields from the TextEntries table. We would, more aptly, specify which fields we want and their preferred order.

/* Example 2: Basic Query with Field List */
SELECT TextEntryID, TableID, ID, TextTypeID, TextStatusID, Purpose, TextDate, LoginID, EnteredDate, AuthorConID, Remarks, TextEntryHTML, TextEntry, LanguageID, Systimestamp FROM TextEntries

Okay, but there are generally other data that we would like, such as the actual Text Type, instead of some foreign key. We can incorporate additional tables into our query, using a JOIN, which you may be familiar with from Crystal Reports.

/* Example 3: Query with a Single Join */
SELECT TE.TextEntryID
,TE.TableID ,TE.ID
,TE.TextTypeID ,TT.TextType
,TE.TextStatusID
,TE.Purpose
,TE.TextDate
,TE.LoginID ,TE.EnteredDate
,TE.AuthorConID
,TE.Remarks
,TE.TextEntryHTML ,TE.TextEntry
,TE.LanguageID
,TE.Systimestamp
FROM TextEntries TE
LEFT JOIN TextTypes TT ON TE.TextTypeID = TT.TextTypeID

Now that there are multiple tables in our script, we need to specify which table a field is being retrieved from. I have given TextEntries a nickname or alias of TE, which I prefix onto the field names.

But we may be interested in the Status or content Language of a Text Entry. As with Crystal Reports, we can join to multiple tables.

/* Example 4: Query with Multiple Joins */
SELECT TE.TextEntryID
,TE.TableID ,TE.ID
,TE.TextTypeID ,TT.TextType
,TE.TextStatusID ,TS.TextStatus
,TE.Purpose
,TE.TextDate
,TE.LoginID ,TE.EnteredDate
,TE.AuthorConID ,C.DisplayName ,C.AlphaSort 
,TE.Remarks
,TE.TextEntryHTML ,TE.TextEntry
,TE.LanguageID ,L.Mnemonic AS Language
,TE.Systimestamp
FROM TextEntries TE
LEFT JOIN TextTypes TT ON TE.TextTypeID = TT.TextTypeID
LEFT JOIN TextStatuses TS ON TE.TextStatusID = TS.TextStatusID
LEFT JOIN Constituents C ON TE.AuthorConID = C.ConstituentID
LEFT JOIN DDLanguages L ON TE.LanguageID = L.LanguageID

Since the use of the Languages.Mnemonic field may not seem intuitive, we can alias it as Language in our query. We have also included both the author’s DisplayName and AlphaSort, since both are commonly used in reports. Our query is complete, and retrieves all the data pertaining to a Text Entry.

Final Product

/* Final: Create a View */
CREATE VIEW vMyTextEntries AS

SELECT TE.TextEntryID
,TE.TableID ,TE.ID
,TE.TextTypeID ,TT.TextType
,TE.TextStatusID ,TS.TextStatus
,TE.Purpose
,TE.TextDate
,TE.LoginID ,TE.EnteredDate
,TE.AuthorConID ,C.DisplayName ,C.AlphaSort 
,TE.Remarks
,TE.TextEntryHTML ,TE.TextEntry
,TE.LanguageID ,L.Mnemonic AS Language
,TE.Systimestamp
FROM TextEntries TE
LEFT JOIN TextTypes TT ON TE.TextTypeID = TT.TextTypeID
LEFT JOIN TextStatuses TS ON TE.TextStatusID = TS.TextStatusID
LEFT JOIN Constituents C ON TE.AuthorConID = C.ConstituentID
LEFT JOIN DDLanguages L ON TE.LanguageID = L.LanguageID

With one final modification, our query is transformed into a SQL View called vMyTextEntries. You can query on it and use it in Crystal Reports–In fact, this view can help to simplify report writing, since you no longer have to include or join to the Text Types, TextStatuses, Constituents, or DDLanguages tables within your Crystal Report.

There are some practical limitations to SQL Views you should be aware of. Firstly, every column has to have a name and every column name within the view has to be unique. If you were including fields with the same name from two different tables, you could alias one, or both, of them. Secondly, while you can include virtually any function, aggregation (ie, DISTINCT or GROUP BY clause), subquery, or condition (ie, WHERE clause) to your SELECT statement, SQL Views will not allow an ORDER BY clause.

Granting Permissions

Now that you have created your view you will need to give users the ability to SELECT records from it. Refresh the Views node for your database in SQL Server Management Studio (SSMS). Right-click on vMyTextEntries and choose “Properties“. Choose the “Permissions” page. Search for the group named “TMSUsers”, and then grant them SELECT permissions. Click “OK.”

You’re now free to use this view in your reports.