[SalesForce] Query of Tasks for Custom Object

I have a list of Custom Objects, created by customers each day – they can be considered like trouble tickets. Against each one, is a Task created when our agents make an outbound call back to the person. This is through the Salesforce Phone CTI.

I want to list each NEW Custom Object record crated TODAY, and list beside each record:
Blank if no tasks done (ie no one called the customer)
Username and call time if a call has been made

I have the following:

SELECT Id, Owner.Name, Subject
from Task
where Whatid in (SELECT Id 
FROM Registration_Object__c 
WHERE (CreatedDate = TODAY)
AND RecordType.Name = 'Media Registration')

which lists the details I want, but its limited to only those Media Registrations that have a task associated (ie the ones with no task yet aren't listed with blanks)

In SQL its a join that would do it but I'm new to SOQL, so can't figure out the relationship for the query, or if maybe it needs to be 2 queries.

I've tried a few permutations of

Select id, Tenant_Name__c, Tenant_Phone1__c, (SELECT Id, Owner.Name, Subject from Task)
FROM Registration_Object__c 
WHERE (CreatedDate = TODAY
AND RecordType.Name = 'Media Registration')

which I know is wrong.

I should have the subquery related back to the Id from the main Select in some format such as:

...where WhatId in (the Id from the main query)

Or should I do it as 2 queries? (pull all Ids created Today, then pull all Tasks with a WhatId in that list?)

Thanks

Best Answer

If you use the Child Relationship Name Tasks you should be able to do it in one query.

Example using Account:

SELECT Id, Name, (SELECT Id, Subject FROM Tasks) FROM Account WHERE CreatedDate = TODAY

Related Topic