0

Does any one know how to write an SOQL in apex for below scenario

" There are two object say "Countries" and "MappedCountries" where Countries object will have a complete list of Countries and MappedCountries will only have a few Countries(it is like subset of Countries object ).

Now I want an SOQL apex function like " I want entries in Countries object type which are not matched in MappedCountries object type".

For eg : Countries Object type consists of IND AUS NZ ENG and MappedCountries object type consists of NZ AUS then the query should obtain the unmatched result from two object types as IND ENG.

Help me regarding this.

eyescream
  • 18,088
  • 2
  • 34
  • 46
565
  • 615
  • 2
  • 10
  • 19

2 Answers2

1

If there's no direct relationship (a lookup) between them I think you'll need 2 queries to get the data. That's because NOT IN likes an explicit list of constants or subquery that returns Ids (so strings with country names won't work). For example this does not even compile:

SELECT Id, Name FROM Account WHERE Name NOT IN (SELECT Name FROM Contact)

So you need something like this:

Set<String> countriesToExclude = new Set<String>();
for(Mapped_Country__c mc : [SELECT Name FROM Mapped_Country__c]){
    countriesToExclude.add(mc.Name);
}

List<Country__c> excludedCountries = [SELECT Name FROM Country__c WHERE Name NOT IN :countriesToExclude];

Would be much, much easier if you'd have for example object called Selection__c and it'd have a related list of Country__c records... You could simply write something similar to this that returns Contacts without Account:

SELECT Id, Name FROM Contact WHERE AccountId = null
eyescream
  • 18,088
  • 2
  • 34
  • 46
-1

I think you can try this:

Select Id, Name From Countries__c Where Name NOT IN (Select Name From MappedCountries)
mast0r
  • 820
  • 5
  • 13
  • Yes, it will work: [Read this doc](http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select_comparisonoperators.htm) – mast0r Dec 05 '12 at 10:45