Search code examples
salesforcesoql

how do i check with SOQL if any objects in list fulfilling condition

I have this soql query

SELECT Id, Name, 
    (SELECT ContactPointAddress.Id, ContactPointAddress.IsDefault FROM ContactPointAddresses)
FROM Account

which list all contact point address for each account. Can i somehow add a where clause so i can get the accounts that does not have any contact point address or where the list of contactpointaddresss dont have one where isDefeault = true

Solution

  • "anti joins" (NOT IN), like "show me accounts that don't have any contacts, why do we even bother keeping them" would be

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

    You can do similar thing in reports, cross filter.

    Try

    WHERE Id NOT IN (SELECT AccountId FROM ContactPointAddress WHERE IsDefault = true)