Salesforce Query

Simple Query:
=============
1. SELECT Contact.Firstname, Contact.Account.Name FROM Contact

Must have read level permission

2. SELECT Account.Name, (SELECT Contact.LastName FROM Account.Contacts)

FROM Account

WHERE Clause
=============
- SELECT Id FROM Contact WHERE Name LIKE 'A%' AND
MailingState='California'
- SELECT Name FROM Account WHERE CreatedDate > 2011-04-26T10:00:00-08:00

You can use date or datetime values, or date literals. The format for date
and dateTime fields are different.
- SELECT Amount FROM Opportunity WHERE CALENDAR_YEAR(CreatedDate) = 2011

Null in SOQL Queries:

======================
SELECT AccountId FROM Event WHERE ActivityDate != null

Querying Multi-Select Picklists

===============================
SELECT Id, MSP1__c from CustObj__c WHERE MSP1__c includes ('AAA;BBB','CCC')

Semi-Joins with IN and Anti-Joins with NOT IN

=============================================
=============================================
You can query values in a field where another field on the same object has
a specified set of values, using IN. For example:
- SELECT Name FROM Account WHERE BillingState IN ('California', 'New
York')

If you filter by an ID field, you can create parent-to-child semi- or anti-

joins, such as Account to Contact.
If you filter by a reference field, you can also create child-to-child
semi- or anti-joins, such as Contact to Opportunity, or child-to-parent
semi- or anti-joins, such as Opportunity to Account.

==============================================
ID field Semi-Join

Parent-to-child:
You can include a semi-join in a WHERE clause. For example, the following
query returns account IDs if an associated opportunity
is lost:
SELECT Id, Name FROM Account WHERE Id IN ( SELECT AccountId FROM Opportunity
WHERE StageName = 'Closed Lost')

This example is a parent-to-child semi-join from Account to Opportunity.

Notice that the left operand, Id, of the IN clause
is an ID field. The subquery returns a single field of the same type as
the field to which it is compared.

Akshay chowdhry
 Salesforce Query

============================================

Reference Field Semi-Join

Child-to-parent:
The following query returns task IDs for all contacts in Twin Falls:
- SELECT Id FROM Task WHERE WhoId IN (SELECT Id FROM Contact WHERE
MailingCity = 'Twin Falls'ID

==========================
ID field Anti-Join
Parent-to-child:
The following query returns account IDs for all accounts that do not have
any open opportunities:
SELECT Id FROM Account WHERE Id NOT IN(SELECT AccountId FROM Opportunity
WHERE IsClosed = false)

============================
Reference Field Anti-Join
Child-to-child:
The following query returns opportunity IDs for all contacts whose source
is not Web:
SELECT Id FROM Opportunity WHERE AccountId NOT IN (SELECT AccountId FROM
Contact WHERE LeadSource = 'Web')

This example is a child-to-child anti-join from Opportunity to Contact

===========================

Multiple Semi-Joins or Anti-Joins

================================
===============================
SELECT Id, Name FROM Account WHERE Id IN(SELECT AccountId FROM Contact
WHERE LastName LIKE 'apple%')
AND
Id IN(SELECT AccountId FROM Opportunity WHERE isClosed = false)

Please note: OR are not allowed.

=================================

ORDER BY:
ORDER BY fieldOrderByList {ASC|DESC} [NULLS {FIRST|LAST}] ]

SELECT Name FROM Account ORDER BY Name DESC NULLS LAST

========================================

LIMIT:

OFFSET:

SELECT AccountNumber FROM Account ORDER BY Name LIMIT 10 OFFSET 4

Akshay chowdhry
 Salesforce Query
GROUP By:

SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource

GROUP BY ROLLUP:
SELECT LeadSource, COUNT(Name) cnt FROM Lead GROUP BY ROLLUP(LeadSource)

================================
HAVING: The HAVING clause is similar to a WHERE clause. The difference is
that you can include aggregate functions in a HAVING clause, but not in a
WHERE clause.
For example: Query returns accounts with duplicate names:
SELECT Name, Count(Id) FROM Account GROUP BY Name HAVING Count(Id) > 1

====================================
AGGREGATE FUNCTIONS:
Two: Count() , Count(fieldName)
If you are using a GROUP BY clause, use COUNT(fieldName) instead of COUNT().
YOu can use count with LIMIT, but you cann't use count() with ORDER BY,
GROUP BY, instead can use count(fieldName)

Akshay chowdhry