[soql].something not behaving as expected

Question

The following query fails with this error message:

Illegal assignment from Account to List

list <account> actlist =  [select   account.id from contact ].account;

The following query is successful but I want all the accounts, not just 1...

list <account> actlist =  new list <account>{[select   account.id from contact limit 1].account};

I have two questions:

1. Is there anything I can do to the first statement to get all of the accounts into variable actlist? I tried syntax like list <account> actlist = [[select account.id from contact ].account]; and list <account> actlist = [select account.id from contact ].account[];
2. Is there any way I can select from CONTACT and end up with a list of ACCOUNTs in a single line of code?

Answer 1

As for your questions, if you want to query all Account records which have a child Contact, the correct approach would be a Left Inner Join:

List<Account> records = [
    SELECT Name FROM Account WHERE Id IN (
        SELECT AccountId FROM Contact
    )
];

No matter what you do, the type of List you get back is going to match your top-level FROM clause. If you want to assign a query result directly to a List<Account>, you need to query Account directly. Full stop.

As mentioned in the other answers already, if you want to include Contact data, you would use a Left Outer Join. These two types of join can be used in the same query:

List<Account> records = [
    SELECT Name, (SELECT Name FROM Contacts)
    FROM Account WHERE Id IN (
        SELECT AccountId FROM Contact
    )
];

Answer 2

You can get but based on number of accounts in your account, you might hit governor limits. With below code, you can retrieve up to 50000 records. If you have more accounts than that, you will get below error

too many query rows :50001

Code:

list <account> actlist =  [select  id,(select name,mailingcity from contacts) from account limit 200];

If you want to get contact records,

for(account acc:actlist){
system.debug(acc.contacts);
}