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
peterg012peterg012 

SOQL Statement Help

Hi,

 

Hope someone can lend a hand here.  I have a list of email addresses (:emails).  I would like to then query all the email addresses against our contacts email addresses, then if it finds only 1 contact with that email address, return the ID etc.  So it must pretty much ignore not match for emails and ignore where count email != 1.

 

I have tried researching this but I battling to find an answer.  I have currently got the below SOQL statement:

 

[SELECT Id, AccountId, COUNT(Email) FROM Contact WHERE Email IN :emails GROUP BY Id, AccountId HAVING COUNT(Email) = 1];

 

Thanks in advance,

Peter

minkeshminkesh

have tried to run this query in the eclipse ? and the relation is masterdetail relationShip?
peterg012peterg012

Hi,

 

I have got the Force.com IDE, but I don't think it lets u test SOQL queries.

 

Peter
Ritesh AswaneyRitesh Aswaney

The Force.com IDE does let you run SOQL Queries. Navigate to the Project Explorer and click on the Schema object (will typically have the same name as your project and is usually at the end of your project contents in the left hand side pane)

 

If you double click that, it brings up a window where you can run SOQL Queries.

 

I ran this one, and it does return results, so the query is well formed.

 

I dont quite understand your requirements, so I'm not sure if the result set is in line with your expectation.
peterg012peterg012

Hi,

 

Thanks, I never knew that you could run queries there. :) Basically the variable ":email"  is a list of email addresses and I would like to only return the contacts from Saleforce where only 1 instance of the email address is found. e.g.

 

List (:email) variable

 

john@abc.com

pam@abc.com

jack@abc.com

 

Contacts

 

John Doe - Email: john@abc.com

John Doe 2 - Email john@abc.com

Jack Doe - Email jack@abc.com

Pam Doe - Email pam@abc.com

etc...

 

So the query should only return: Pam and Jack, because 2 contacts have jack@abc.com .   Does this make sense?

 

Thanks

Peter

 

 
joshbirkjoshbirk

Like so?

 

Select Id, Email From Contact Group By Email, Id HAVING Count(Email) = 1

 

I think your first post was close, just maybe the group by needed updating?
peterg012peterg012

Hi,

 

Thanks for the suggestion, I have tries that, but it still returns multiple records for the same email address.  I think the "WHERE Email IN :emails", causes this.  So I am still uncertain what the solution is?

 

Thanks

Peter
joshbirkjoshbirk

I gotya.  I think it's the Group By on the multiple fields.  This:

 

 

SELECT Email, Count(Id)
FROM Contact
GROUP BY Email
HAVING Count(Id) = 1
Seems to bring back just a list of unique emails.  You may need to then do a follow up query based on records with that list of email addresses to get Id, Name, etc.  

 
peterg012peterg012

Hi, I managed to get the following query working, which returns contacts where email count = 1:

 

[SELECT Email FROM Contact GROUP BY ROLLUP(Email) HAVING Email IN :emailList AND COUNT(Email) = 1];

 

This seems to do the trick for me.  Thanks to all for the help and suggestions.

 

Peter
RichTheCoderRichTheCoder

In other contexts, you must get one Contact for each Email Address (if any such Contact exists), including addresses that match more than one Contact.

The following Apex snippet yields a map that gets one Contact per email address:

// li_owem is a list of the email addresses of the all the owners.
// List all matching contacts via SOQL, then arbitrarily select 
// the first contact for each email address
map<String, Contact> ma_emCO = new map<String, Contact>();
set<Contact> li_CO = new set<Contact>([select Email, Id from Contact where Email in :li_owem]) ;  
//loop over contacts, add each email address to the map one time only
for ( Contact coco : li_CO) {
	if ( ma_emCO.get(coco.Email)== null ) {
		ma_emCO.put(coco.Email, coco);
}}