Advanced SQL

Joining, Calculating, Aggregating, Correlating

Before digging into Advanced topics, you may want to review the Intermediate and Beginning articles.

Joining Tables

To refer to more than one table in a query, a join is used to specify the tables and fields used. The default join is INNER, but LEFT join is also sometimes used in MinistryPlatform.

[INNER] JOIN

If the type of JOIN is not specified, it is an INNER JOIN. This type of JOIN will only return results if a match exists in both tables. If there is no record in the joined table, nothing is returned in the result set.

This example of a field in a Congregations View uses a JOIN to count the active Contacts for each Congregation:

(SELECT COUNT(C.Contact_ID)   
FROM Households H JOIN Contacts AS C     
ON C.Household_ID = H.Household_ID   
WHERE H.Congregation_ID = Congregations.Congregation_ID  AND C.Contact_Status_ID IN (1,4,5)) AS [Contact Count]

The JOIN statement is used in conjunction with the ON clause to specify which two tables are joined an which fields used in the join.

LEFT JOIN

The join used under the hood by the Advanced Search Tool's Table Lookup Convention is the LEFT JOIN. This includes a row in the result even for NULL values in the joined table.

When you want to include values from another table and want NULL values in the joined table to be included, use a LEFT JOIN. This implies "include everything in the left table, even if there is a NULL in the right table."

For example, the following join will return a count of Contacts with no Country specified in the Address, even if there is no Household or Address record.

SELECT COUNT(C.Contact_ID)   
FROM Contacts C   
LEFT JOIN Households H ON H.Household_ID = C.Household_ID  
LEFT JOIN Addresses A ON A.Address_ID = H.Address_ID    
WHERE A.Country_Code IS NULL

Table Aliases

When writing SQL statements which refer to more than one table, it is good practice (and often necessary) to use table aliases.

Table Aliases accomplish the following:

Make complex queries more readable.
Make your intention more clear.
Reduce the likelihood of unintentional relationships between tables.
Allow you to refer to the same table in different relationships.

Consider the following subquery used to get a second Head of Household:

(SELECT TOP 1 C.First_Name FROM Contacts C   
WHERE C.Household_ID = Households.Household_ID AND C.Household_Position_ID = 1    
 AND EXISTS (SELECT 1 FROM Contacts C2 WHERE C2.Household_Position_ID = 1       
AND C2.Household_ID = C.Household_ID GROUP BY C2.Household_ID HAVING COUNT(C2.Contact_ID) > 1)   
GROUP BY C.Contact_ID, C.First_Name, C.Gender_ID ORDER BY Gender_ID DESC) AS [Head 2]

In this example, C is an alias for Contacts and C2 is also an alias for Contacts. The table is used twice in the query, but joined in different ways.

Calculated Fields

A calculated field may be as simple as a mathematical calculation on a field value or as complicated as a correlated subquery.

Here is an example of a simple calculation as a calculated Field. In this example, the number of minutes for event setup is added to the start date to get the beginning of the event reservation:

DATEADD(MINUTE, -1 * Events.Minutes_For_Setup, Events.Event_Start_Date) AS Reservation_Start

A calculated field must always return a single value. There are a number of techniques to accomplish this depending on the context:

Aggregate Functions
SELECT TOP 1
STUFF
DISTINCT
GROUP BY

Aggregate Functions

Aggregate Functions return a value for a group of records. This is useful for counting matching records, finding the first or last in a series of records, among other things.

The following field example from a Households View will count the number of Contacts in the Household:

(SELECT COUNT(C.Contact_ID)   FROM Contacts C   WHERE C.Household_ID = Households.Household_ID) AS [Contact Count]

SELECT TOP N

The "TOP N" construct reduces the number of items returned. The primary uses of this construct in MinistryPlatform are:

Returning a single value for a calculated field (multiple values will cause an error).
Returning 1 in an EXISTS function.

Which items are returned is determined by the ORDER BY if specified, or by the query engine if no order is specified.

In this example, SELECT TOP 1 is used to return the first match for Head of Household ordered by the Gender. This would return a male head first if there is one:

,(SELECT TOP 1 C.First_Name FROM Contacts C   
WHERE C.Household_ID = Households.Household_ID   
AND C.Household_Position_ID = 1 ORDER BY C.Gender_ID) AS [Head1_First Name]

STUFF

The STUFF Function combined with FOR XML can be used to return a list of values as a single text value.

This example returns a comma-delimited list of Rooms for an Event:

STUFF((SELECT ', ' + R.Room_Name  
FROM Rooms R JOIN Event_Rooms ER ON R.Room_ID = ER.Room_ID  
WHERE ER.Event_ID = Events.Event_ID AND ER.Cancelled = 0  
FOR XML PATH('')), 1, 2, '') AS [Event Rooms]

DISTINCT

There are times when you need to return unique values rather than individual records.

For example, you may want to return a list of Users who have edited a record. Rather than list each edit, you want to list each User. Here, the DISTINCT keyword is used to only return unique values for User_Name:

, STUFF((SELECT DISTINCT ', ' + AL.User_Name  
FROM dp_Audit_Log AL  
LEFT OUTER JOIN dp_Audit_Detail AD ON AL.Audit_Item_ID = AD.Audit_Item_ID  
WHERE AL.Record_ID = Contacts.Contact_ID AND AL.Table_Name = 'Contacts'    
AND AL.Date_Time >= GETDATE() - 7     
FOR XML PATH('')), 1, 2, '') AS [Changes By]

The DISTINCT keyword treats NULL as a unique value, so a DISTINCT list will include all values and NULL if there is one in the result set.

DISTINCT may be used in conjunction with Aggregates, STUFF, and other SELECT statements.

If more than one field is included in the SELECT DISTINCT, each unique combination is included.

What would you like to know more about?