function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Ganesh MateGanesh Mate 

Self join on same object

Hi folks,

I'm trying to have self join on same object in SOQL Query,but did'nt work around.

I tried different queries like ,

1) SELECT Id,Name,Type
     FROM Account where Id = 'a0X3600000ACDiQEAX' IN (SELECT Type FROM Account)

2) SELECT (SELECT Type FROM Account),Id,Name,Type
    FROM Account where Id = 'a0X3600000ACDiQEAX'

Thank You.
Alain CabonAlain Cabon
Hi,

SOQL: You cannot query on the same object in a subquery as in the main query. You can write such self semi-join querieswithout using semi-joins or anti-joins. For example, the following self semi-join query is invalid:
  
SELECT Id, Name
FROM Account
WHERE Id IN
  (
    SELECT ParentId
    FROM Account
    WHERE Name = 'myaccount'
  )


https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_comparisonoperators.htm

 
Alain CabonAlain Cabon
By the way, your queries are strange.

You want to find all the accounts having the same type as the account with the Id = 'a0X3600000ACDiQEAX' ?
 
Ganesh MateGanesh Mate
Hey Alain,

Thanks for your reply. 

Acutally may query is giving this output.
User-added image

But i want it in this way,

User-added image

Thank you.
Alain CabonAlain Cabon
Hi Ganesh,

Given your example, it is a rollup.
  
SELECT Type,Name,BillingCountry,count(Id)
FROM Account
GROUP BY ROLLUP(Type,Name,BillingCountry)
order by Type desc,Name desc,BillingCountry desc
Alain CabonAlain Cabon
That is not possible with SOQL. You are confusing either with SQL or a report.

You should explain clearly your need for the result of the request because if you don't understand the result of the ROLLUP that is useless for you.

Open a new question now because no one else will read your question here now.

Your question was about a self join on same object in SOQL (SOLVED now)

Don't repost your initial queries for the new question because they are completely wrong (non sense).