0
SELECT AccountId, Id, FirstName, LastName 
FROM Contact 
WHERE AccountId = [SELECT Id FROM Account WHERE Sic = '6752']

2 Answers 2

2

What you have done is called semi-join. A semi-join is a subquery on another object in an IN clause to restrict the records returned.
The issue lies in the square brackets, you should have used round ones:

SELECT AccountId, Id, FirstName, LastName 
FROM Contact 
WHERE AccountId IN (SELECT Id FROM Account WHERE Sic = '6752')

Semi-joins are usually used in parent-to-child relationship, meaning that the main object is the parent (i.e. Account) and the one in the subquery is the child (i.e. Contact).
The opposite relationship, a child-to-parent relationships, doesn't need a semi-join, since you can use dot notation both in SELECT field list and in WHERE clause as showed by eyescream

SELECT AccountId, Id, FirstName, LastName 
FROM Contact 
WHERE Account.Sic = '6752'

Of course if Sic is a custom field you should use its api name, i.e. WHERE Account.Sic__c = '6752'

Please note that in a child-to-parent relationships query, in order to use the dot notation, the lookup field suffix changes from __c to __r:

SELECT Id, Name, Account__c, Account__r.AccountNumber 
FROM CustomObject__c 
WHERE Account__r.Industry = 'Banking'

Take a look at the documentation to know semi-joins limits

1
  • Thank you so much for this detailed answer. πŸ˜ƒπŸ˜ƒ Jul 31, 2023 at 12:25
0
SELECT AccountId, Id, FirstName, LastName FROM Contact WHERE Account.Sic = '6752'

Read up about relationships, for example https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm

1
  • Thanks, Sir for your guidance πŸ˜ƒ Jul 29, 2023 at 12:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .