Use a left outer join in QueryExpression to query for rows "not in"
You can use a left outer join by using the QueryExpression class to perform a query that filters on the join table, such as to find all contacts who did not have any campaign activities in the past two months. Another common use for this type of a query is to find table rows (records) “not in” a set, such as in these cases:
- Find all leads that have no tasks
- Find all accounts that have no contacts
- Find all leads that have one or more tasks
A left outer join returns each row that satisfies the join of the first input with the second input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values.
You can perform a left outer join in QueryExpression
by using the entityname
column as a condition operator. The entityname
column is valid in conditions, filters, and nested filters.
Find all leads that have no tasks, using an alias
The following example shows how to construct this query:
QueryExpression qx = new QueryExpression("lead");
qx.ColumnSet.AddColumn("subject");
LinkEntity link = qx.AddLink("task", "leadid", "regardingobjectid", JoinOperator.LeftOuter);
link.Columns.AddColumn("subject");
link.EntityAlias = "tsk";
qx.Criteria = new FilterExpression();
qx.Criteria.AddCondition("tsk", "activityid", ConditionOperator.Null);
This is equivalent to the following SQL:
SELECT lead.FullName
FROM Leads as lead
LEFT OUTER JOIN Tasks as ab
ON (lead.leadId = ab.RegardingObjectId)
WHERE ab.RegardingObjectId is null
See also
Build Queries with QueryExpression
Using the QueryExpression Class
Using the QueryByAttribute Class
Σχόλια
https://aka.ms/ContentUserFeedback.
Σύντομα διαθέσιμα: Καθ' όλη τη διάρκεια του 2024 θα καταργήσουμε σταδιακά τα ζητήματα GitHub ως μηχανισμό ανάδρασης για το περιεχόμενο και θα το αντικαταστήσουμε με ένα νέο σύστημα ανάδρασης. Για περισσότερες πληροφορίες, ανατρέξτε στο θέμα:Υποβολή και προβολή σχολίων για