Apex Code Development You need to sign in to do that
Don't have an account?
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
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
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).
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