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
Vinny12Vinny12 

URGENT-----SQL Query on salesforce objects

Hi All,

 

I have 2 objects.

Contacts

Donations

 

Donations is the child object and have the foreign key contact id in Donations.

Each contact may donate any number of times. 

Contact to Donation have one to many relationship. So one contact has many donations.

 

I want to pull all contact records who donated for past 5 years from now. such that contact should have atleast one donation in each and every year.

 

Ex: 2009,2010,2011,2012,2013

I want the contact records who donated atleast once in 2009 and 2010 and 2011 and 2012 and 2013

 

If contact missed to donate in any one of the year , i dont want that record in my result.

 

Please help me with sql query.

Its urgent

 

Thanks

 

 

JohnSchultzJohnSchultz

I haven't tested this (and it looks a bit sloppy), but would a query like the following work for you?

 

select Id, Name from Account where Id in (select Contact from Donation__c where Year__c = 2009) and Id in (select Contact from Donation__c where Year__c = 2010) and Id in (select Contact from Donation__c where Year__c = 2011) and Id in (select Contact from Donation__c where Year__c = 2012) and Id in (select Contact from Donation__c where Year__c = 2013)

 Obviously, if this is what you meant, you could clean it up and use a for loop to build the query.
Vinny12Vinny12
thanks alot for ur reply.

Can you please send me using for loops
Vinny12Vinny12
i have date format as "mm:dd:yy"
JohnSchultzJohnSchultz

Is it an actual date field or just a text field?
neil12neil12
Is vinny here,

It is in date datatype
neil12neil12
but this is going to run for ever.. i tried in this way but didnt succeed
JohnSchultzJohnSchultz

You're right. You can only do 2 sub-queries per query. My bad.

 

How about something like this:

Map<Id, Integer> contactsCount = new Map<Id, Integer>();
Set<Id> goodDonors = new Set<Id>();

for (Donation__c d : Database.query('select Contact__c from Donaction__c where Donation_Date__c <= ' + Date.today().year()-4 + '-01-01 and Contact__c != null') {
	if (!contactsCount.containsKey( d.Contact__c )) {
		contactsCount.put(d.Contact__c, 0);
	}
	
	Integer count = contactsCount.get(d.Contact__c) + 1;
	contactsCount.put(d.Contact__c, count);
}

for (Id contactId : contactsCount.keySet()) {
	if (contactsCount.get(contactId) == 5) {
		goodDonors.add(contactId);
	}
}

if (!goodDonors.isEmpty()) {
	List<Contact> contacts = new List<Contact>([select Id, Name, ....... from Contact where Id in : goodDonors]);
}

 
neil12neil12
Hey john, thanks alot for your response.!!

I got the answer. We can do this by using group by and having clause.

Once again thanks alot