[SalesForce] SOQL join in a Parent Child for same table (Account)

I want to write this simple soql query where in Account there is a ParentId and with it would want to bring the name of this parent (Hierarchy field)
Something like:

select Id, Name, (select Name from Account__r where Id = Account.ParentId)
From Account where id = [xxxxxxx]

I know my Where statement is wrong, just wanted to clarify what it is I want
I have seen many examples of join but never where it is the same table.

Best Answer

You do not need any additional filters to make sure the children are related to the parent. If you write the below query, each row will have an attribute named ChildAccounts which contains each Account record whose ParentId matches the row's own Id value.

SELECT Name, (SELECT Name FROM ChildAccounts) FROM Account

If you want to select the parent record instead of children, you would instead do:

SELECT Parent.Name FROM Account

Related Solutions

[SalesForce] SOQL joining multiple tables to get account info from record owner

SOQL does not allow you to nest more than one level down in sub-selects, but you can branch several (I think up to 5?) levels upward instead. Restructuring your query, it would normally be valid as follows:
[SELECT Agency_Acronym__c FROM Account WHERE Id IN (SELECT Owner.Contact.AccountId FROM Application__c)]

BUT, since Owner is a polymorphic field (could refer to a User, Group, or other object), we need to do something a bit trickier(link) to filter on it. I have tested the following and it seems to work:

Set<Id> contactsIds = new Map<Id,User>([select ContactId FROM User WHERE Id IN (SELECT OwnerId FROM Application__c WHERE Owner.Type = 'User')]).keyset();
Account[] accounts = new List<Account>([select Agency_Acronym__c from Account where Id in (select AccountId FROM Contact WHERE Id IN :contactsIds)]);

[SalesForce] SOQL: Inner query on lookup field

There's no need for a filter; the sub-query does this for you automatically. The following query should work:

SELECT Name, productcode,
(select productcode from Products__r limit 1)
FROM product2

Note that you can't currently compare fields to each other in SOQL, there's a few Ideas out there for that, but it will likely take a while to see this become available.

Best Answer

Related Solutions

[SalesForce] SOQL joining multiple tables to get account info from record owner

[SalesForce] SOQL: Inner query on lookup field

Related Topic