+ Start a Discussion
prbprb 

SOQL Question

I'm struggling a bit to write a simple join query using the 'relationship query' syntax of SOQL.
Could someone please post any ideas about how to obtain the following info in a single SOQL command.
Here is the pseudo code:
Get all contacts for accounts that have assets with product='X'
I know I can iterate through all assets and grab each account one at a time but I thought there must be a better way.

I've tried the following which throws SOQL syntax errors:

Select a.Id, a.AccountNumber, a.Name, (SELECT id,lastname,firstname,email FROM Contacts), (SELECT status, Product2Id from Assets ) from Account a where a.Assets.Product2Id = '01t40000000bgY7AAI'


Michael SnowMichael Snow
I don't think that you can do that.  You can try:
Select a.Id, a.AccountNumber, a.Name, (SELECT id,lastname,firstname,email FROM Contacts),
(SELECT status, Product2Id from Assets where Product2Id = '01t40000000bgY7AAI' )
from Account a

But, this will return all accounts, and then only Assets where they have that Product2Id.  You can just make sure that there is at least 1 record of an Asset before using that Account record (and associated Contacts). 

There may be a better way to do this, and I would be very interested in hearing about it.