+ Start a Discussion
pjaenick.ax736pjaenick.ax736 

SOQL multiple WHERE criteria

Greetings!

 

If I have a list of existing records that I wish to query for existence based on Field1, Field2, and Field3, how would I ensure that I don't get a record back where List[1].Field1 matches record X, but List[1].Field2 matches some other record?

 

       ExistingContactsList=[
            SELECT Name, Email, Phone, AccountId
              FROM contact
             WHERE Name in:setNames and Email in:setEmails and Phone in:setPhones and AccountId in:setAccountIds 
            ];

 

Thanks for any help,

Pete

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

You can't do this with a static query, at least not without creating some metadata (a formula field or other field that concatenates both values together). For on-the-fly selection, use a dynamic query; you'll need your query to be formulated like this:

 

SELECT Id, Name, Email, Phone, AccountId
FROM   Contact
WHERE  (Name = 'Some Name 1' AND Email = 'Some Email 1' AND Phone = 'Some Phone 1' AND AccountId = 'Some AccountId 1') OR
       (Name = 'Some Name 2' AND Email = 'Some Email 2' AND Phone = 'Some Phone 2' AND AccountId = 'Some AccountId 2') ...

You can build a list of filters into a string array, then use string.join to join them together to make the query easier to buid. Note that there's a 20,000 character limit on SOQL, so you may need to break the query up into several smaller parts using some sort of other logic (about every 50 set of criteria would probably approach this limit).

All Answers

sfdcfoxsfdcfox

You can't do this with a static query, at least not without creating some metadata (a formula field or other field that concatenates both values together). For on-the-fly selection, use a dynamic query; you'll need your query to be formulated like this:

 

SELECT Id, Name, Email, Phone, AccountId
FROM   Contact
WHERE  (Name = 'Some Name 1' AND Email = 'Some Email 1' AND Phone = 'Some Phone 1' AND AccountId = 'Some AccountId 1') OR
       (Name = 'Some Name 2' AND Email = 'Some Email 2' AND Phone = 'Some Phone 2' AND AccountId = 'Some AccountId 2') ...

You can build a list of filters into a string array, then use string.join to join them together to make the query easier to buid. Note that there's a 20,000 character limit on SOQL, so you may need to break the query up into several smaller parts using some sort of other logic (about every 50 set of criteria would probably approach this limit).

This was selected as the best answer
pjaenick.ax736pjaenick.ax736

Thank you !  I've decided to stick with my initial query, then cycle through the results list to find true row-by-row matches.

 

Was hopeful there was a short-cut I was missing.

 

Thanks,

Pete

 

sfdcfoxsfdcfox
That'll work in the most usual cases. Just remember that they are multiplicative, such that five names, five phone numbers, five emails, and five account IDs results in 625 combinations, despite needing just 5 results. This probably won't matter in most cases, but might be problematic for common names, etc.