JOIN

Joining Tables

To refer to more than one table in a query, use a JOIN to specify the tables and fields used. The default JOIN is INNER, but you may sometimes use LEFT JOIN in MinistryPlatform. In the following image, circle A represents one table and circle B represents another table. The orange represents where the JOIN finds a match.

[INNER] JOIN

If the type of JOIN is not specified, it is an INNER JOIN. This type of JOIN only returns 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]

Use the JOIN statement with the ON clause to specify which two tables are joined and 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

SELECT TOP N

The TOP N construct reduces the number of items returned. "N" represents the number of items you want this query to return. 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.

If specified, the ORDER BY clause determines which items the query returns. If there is no ORDER BY clause, the query engine determines which items the query returns.

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

Use the STUFF Function with FOR XML 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 includes all values and NULL if there is one in the result set.

You may use DISTINCT with Aggregates, STUFF, and other SELECT statements.

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

Table Aliases

When writing SQL statements for 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.

See Also:

• Database Relationships
• Primary & Foreign Keys
• Table Lookup Convention

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]

See Also:

• SQL Functions: Aggregate Functions