This article follows on from a previous article "Using Joins in Web Services". That article discussed how views that are linked into CRM and described as 'tables' in metadata can be used to provide access to data from multiple tables within a single webservice request.

In that article I only considered joins and not subqueries. But subqueries are a way of restricting information from one table based on the information in another. A subquery is a query embedded within the WHERE clause of another query to further restrict data returned by the query.

An example of a subquery is

SELECT comp_companyid, comp_name, comp_type
FROM company
WHERE comp_companyid =
(SELECT pers_primarycompanyid
FROM person
WHERE pers_mailrestriction is null)

The subquery can stand on its own. This query is retrieving the details of companies which have person who have not opted out of mailings.

A correlated subquery is a SELECT statement nested inside another SQL statement, which contains a reference to one or more columns in the outer query. An example of a correlated subquery is

SELECT comp_companyid, comp_name, comp_type
FROM company
WHERE exists
(SELECT oppo_opportunityid
FROM opportunity
WHERE
oppo_status = 'Won' and
oppo_primarycompanyid = comp_companyid)

The subquery addressing the opportunity table is joined to the outer query on the company table. This query is finding those companies to whom we have sold.

Subqueries form part of a where clause and several web service methods allow us to pass where clauses.

CRM.query(WhereClause, EntityName);
CRM.queryrecord(ListofColumns, WhereClause, EntityName, OrderByColumn);
CRM.queryidnodate(WhereClause, EntityName, IncludeDeleteflag);

We can use subqueries in these methods

Example 1

string mySQL = " comp_companyid = (SELECT pers_primarycompanyid FROM person";
mySQL += " WHERE pers_mailrestriction is null)";
CRM.queryrecord("comp_companyid, comp_name, comp_type", mySQL, "company", "comp_name");

Example 2

string mySQL = " exists (SELECT oppo_opportunityid FROM opportunity WHERE ";
mySQL +=" oppo_status = 'Won' and oppo_primarycompanyid = comp_companyid)";
CRM.queryidnodate(mySQL, "company", false);