[SalesForce] SOQL to get all tasks of opportunities for an account

I am still new to Salesforce and haven't quite wrapped my head around SOQL queries yet. I still tend to think more in terms of SQL.
Anyway, I am trying to write a statement that gives me all "Call Tasks" that are attached to all Opportunities of an Account.

SELECT Id, (SELECT Id FROM Opporunity, (SELECT Id FROM Task WHERE Type="Call") FROM Account

Any ideas in how to make this work?
Tia.

Best Answer

This is one way:

select Id, Subject
from Task
where WhatId in (select Id from Opportunity where AccountId = :accountId)
and Type = 'Call'
order by Subject

The Relationship Queries documentation is well worth reading.

PS

On the subject of where to get the Account ID, probably the simplest way in a controller extension is this, though in the "new" case there won't be an ID hence the alternate logic:

public with sharing class MyController {
    private Id accountId;
    public MyController(ApexPages.StandardController sc) {
        accountId = sc.getId();
    }
    ...
    public Task[] getTasks() {
        if (accountId != null) {
            return [
                    select Id, Subject
                    from Task
                    where WhatId in (select Id from Opportunity where AccountId = :accountId)
                    and Type = 'Call'
                    order by Subject
                    ];
        } else {
            return new Task[] {};
        }
    }
}
Related Topic