Before digging into Advanced topics, you may want to review the Intermediate and Beginning articles.
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 MP.
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.
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
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:
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:
A calculated field may be as simple as a mathematical calculation on a field value or as complicated as a correlated subquery (see below).
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 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:
The "TOP N" construct reduces the number of items returned. The primary uses of this construct in MinistryPlatform are:
Which items are returned is determined by the ORDER BY (see above) 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]
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]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.
The EXISTS function acts on a query and returns true if there are any matches. Conversely, it returns false if there are no matches. This is useful in View filters to limit the records in grid.
In the following example, a Contacts View with the following filter will only return Contacts that are currently active in a Group:
EXISTS(SELECT GP.Group_Participant_ID FROM Group_Participants GP
JOIN Groups G ON G.Group_ID = GP.Group_ID
WHERE GP.Participant_ID = Contacts.Participant_Record
AND GETDATE() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date,GETDATE()+1))See EXISTS Function
A query used to calculate a field or filter a view is called a subquery. It is a query inside the larger query (the View). In most cases the subquery is correlated with the view. This is done using a WHERE clause in which one or more fields in the subquery are tied to a field in the View.
In the following example, a Contacts View with the following filter will only return Contacts that are currently active in a Group. The highlighted portion is the clause that correlates the subquery with the outer query (view).
EXISTS(SELECT GP.Group_Participant_ID
FROM Group_Participants GP JOIN Groups G ON G.Group_ID = GP.Group_ID
WHERE GP.Participant_ID = Contacts.Participant_Record
AND GETDATE() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date,GETDATE()+1))This same technique may be used in a calculated field to correlate the the calculation with the record in the view.