When you're putting a subquery in your SELECT clause (i.e. you're querying to see which child records are related to the main target of your outer query), you need to use the child relationship name rather than the normal label for the child SObject.
In your case, that means you need to use Attachments (the plural of the label) rather than Attachment.
From Understanding Relationship Names
For parent-to-child relationships, the parent object has a name for the child relationship that is unique to the parent, the plural of the child object name. For example, Account has child relationships to Assets, Cases, and Contacts among other objects, and has a relationshipName for each, Assets, Cases, and Contacts.These relationships can be traversed only in the SELECT clause, using a nested SOQL query. For example:
SELECT Account.Name, (SELECT Contact.FirstName, Contact.LastName FROM Account.Contacts) FROM Account
This query returns all accounts, and for each account, the first and last name of each contact associated with (the child of) that account.
The Account.Contacts bit isn't strictly necessary. Just Contacts would do.
The "plural of the child object name" rule works for most standard relationship fields and some custom relationship fields (with an "__r" tacked on to the end), but not for everything.
The standard self-relationship on Account (ParentId), for example, has a child relationship name of ChildAccounts.
There are a couple of different ways to figure out what the child relationship name is, but probably the quickest/easiest is to run some anonymous apex to get that information from the SObject Describe info.
// We get at the child relationships from the "Parent" object
Schema.DescribeSObjectResult dso = Account.SObjectType.getDescribe();
for(Schema.ChildRelationship childRel :dso.getChildRelationships()){
// getChildSObject() returns an SObject token, and we can directly compare it to
// <sobject Name>.SObjectType
// Limiting to a single object (or you could store SObject tokens in a set and do
// .contains(childRel.getChildSObject()) ) just helps cut down on clutter.
// There can be more than one child relationship name to a given object (but the child
// relationship names will be different)
if(childRel.getChildSObject() == Attachment.SObjectType){
system.debug('Child relationship name = ' + childRel.getRelationshipName());
}
}
Bonus help
You can do away with your WHERE clause in your subquery.
Since you are in a subquery where Account is the main object, you will only ever get results that are related to one of the records from your outer query.
The attachment's ParentId will never be null. If it were, then it wouldn't show up in the results of your subquery (which only returns child records that are related to one of the parent records your outer query returns).
Furthermore, the records your subquery returns are guaranteed to always be related to the main object, so Parent.Type will always be Account (for this particular query).
The overall query itself implicitly adding those restrictions, much like how if you query SELECT Parent.Name FROM Account, you don't need to check that Parent is actually an Account. The definition of the field itself prevents anything other than an Id for Account to be placed into that field.
