I figured it wouldn’t be such a bad idea for me to post a list of advanced Salesforce.com Apex SOQL queries. This should be a good reference for the community as well as for my own personal reference.
SOQL OUTER JOIN
[Select Name, Account__r.Name From Custom_Object__c Where Id = ‘Some Id’]
This query takes advantage of the parent account relationship to the custom object. Rather than issuing two query statements, one for the Custom Object related to the record with an id of ‘Some Id’, and another for the Account related to the same record with an id of ‘Some Id’, you can retrieve both values in the same query. Just as you can retrieve the account name, you can also retrieve any other field from the account using the ‘__r’ syntax. The correct syntax for you specific query can be found in the eclipse schema explorer.
Salesforce queries are outer join by default, however when you add a where clause on the related parent, the query becomes an inner join. Since in this instance Account is the direct parent of Custom Object, filtering out all the null parent values returns the parent record with the child record, creating an inner join.
SOQL INNER JOIN
[Select Name, Account__r.Name From Custom_Object__c Where Account__c != null]
Parent to Child Query, Filter on Child using Sub-Queries
This query returns all of the parent records, but will also return the child records that have a Field__c value of ‘Platypus’.
[Select Id, Name, (Select Id From Custom_Object__c Where Field__c = ‘Platypus’) From Account]
The following ‘Semi-Join’ query returns only those records with a child record which has a Field__c value of ‘Platypus’. Semi join queries are meant to be read from the bottom up.
[Select Id, Name From Account Where Id IN (Select Account__c From Custom_Object__c Where Field__c = ‘Platypus’)]
Conversely we can use an ‘Anti-Join’ query which would exclude records from the parent object, which is to say that the parent object which has child object that contains value x, will not be returned. A Semi Join query uses the IN keyword, while an Anti-Join query uses the NOT IN keyword.
[Select Id, Name From Account Where Id NOT IN (Select Account__c From Custom_Object__c Where Field__c = ‘Platypus’)]
Note: Subqueries cannot be nested and cannot contain the OR, count(), ORDER BY, or LIMIT keywords.
It is possible to query based on field values even if that field is a multi-select picklist. The following query will return records that have a multi-select picklist field value selection of Jacket and Shirt and Shoes OR a selection of Hat. The semicolon is used to express which multiple values have been selected, in no particular order.
[Select Id, Name From Account Where Field__c INCLUDES (‘Jacket; Shirt; Shoes’, ‘Hat)]
You may also substitute INCLUDES for EXCLUDES.
So that’s advanced soql queries in a nutshell. And to be honest those are not the most complicated of soql queries, but I would say they are between intermediate and advanced. Anyway let me know if this post helps you at all.
Thanks for stopping by.