function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
BWMBWM 

Proper Syntax for SOQL query for Contacts related to Accounts

I am trying to write a SOQL query that returns all Contacts associated with Accounts that meet a certain criteria (Type = 'Prospect')

 

Select * from Contact where AccountID = ???? where Account.Type = 'Prospect'

I'm stuck on the  part with the ???? and also not sure if I'm referencing the "type" field on Account correctly.

Any ideas?

HaroldHHaroldH
If you haven't done so already, go to http://sforce.sourceforge.net/ and download a copy of "sforce Explorer." This will make it easy to create and test your SOQL queries.

You cannot use "select *" in SOQL. You must specify each field by name.

Example:
-- Select Id, Email from Contact where AccountId = '00300000004QjSx'

The account record type is stored as the RecordTypeId field on the Account object. Thus, you can query the RecordType object to find the ID for the RecordType named "Prospect".

Example:
-- Select Id, Name, Description from RecordType where Name = 'Prospect'

That query will get you the proper Id.

Your real challenge is getting a list of Contacts where their Account is of type Prospect. The current version of the sforce API does not support a Join query. Your next best bet is to either run a report, or use two separate queries:

-- Select Id from Account where RecordTypeId = 'abc' (where 'abc' is the Id of the 'Prospect' RecordType retrieved in the query above)

-- Select Id, Email from Contact where AccountId in ('a','b','c'...'z') (this is a list of all the AccountId values retrieved in the previous query)

Good luck.