[SalesForce] SOQL Relationship Query : Compare Child and Parent field value in WHERE clause

Is it possible to compare the Child and Parent field values in a Single SOQL Relationship Query ?

say get me all contacts for an account which doesn't have same name as its parent account.

SELECT Contact.Id, Contact.Name, Account.Id, Account.Name
FROM Contact
WHERE Account.Id = 'xxxxxxxxxxx' AND
      Contact.Name <> Account.Name

or get me all opportunities for an account which has same LeadSource as Account

SELECT Id, LeadSource, Account.LeadSource
FROM Opportunity 
WHERE AccountId in : accIdSet AND 
LeadSource <> Account.LeadSource

Best Answer

I don't think so. The field expression in the where clause can include only 1 field name and not 2. It must be compared to a native value.

http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_fieldexpression.htm#fieldexpression

Related Solutions

[SalesForce] SOQL query minimum Date in WHERE clause

Try this:

list<Group> groups = [select id, name, (SELECT Name, Group__c, Business_Value__c, Meeting_Date__c, GPS_Meeting_Location__Latitude__s, GPS_Meeting_Location__Longitude__s FROM Meetings__r ORDER BY Meeting_Date__c DESC LIMIT 1
 ) from Group where Id =:BomaGroups];

Your limit 1 in the original query applied to the whole of the result set and therefore only returned 1 record. By using a sub-query you can get the records that relate to the groupIds you need. I have guessed at the lookup relationship api name being Meetings__r - this may need adjusting.

[SalesForce] SOQL Child –>Parent —->Child

Not entirely clear about your question. This answer assumes you have two child objects of one parent object.

From A Deeper look at SOQL and Relationship Queries on Force.com you need a Left Outer Join and a Left Inner Join, shown below:

id child1Id = ...;
Parent__c p = [
        select Name, (select ... from Child2s__r where ...)
        from Parent__c
        where Id in (select Parent__c from Child1__c where I = :child1Id)
        ];

String parentName = p.Name;
for (Child2__c child2 : p.Child2s__r) {
    ...
}

You can add where, order by and limit as needed to the Child2__c part of the query.

Best Answer

Related Solutions

[SalesForce] SOQL query minimum Date in WHERE clause

[SalesForce] SOQL Child –>Parent —->Child

Related Topic