function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Ganesh DhanavelGanesh Dhanavel 

self join using soql

Hi

 

 I am trying to do a self join in contact object to get employee and manager id.

This is the query I tried with no-luck :

 

SELECT p.id,p.Employee_ID__c,p.FirstName,p.LastName,(select c.Employee_ID__c FROM Contact c where c.ReportsToId=p.id) from contact p

 

Could some one let me know what I am doing wrong here.

 

Thanks

Best Answer chosen by Admin (Salesforce Developers) 
mshelmanmshelman
Select c.FirstName,c.LastName,c.ReportsTo.FirstName, c.ReportsTo.LastName From Contact c

 

All Answers

liron169liron169

As far as I know, this is impossible.

In past when I needed this I found that I must do the join "manually".

means, retrieve all the relevant records and run over them in 2 nested loop to find the matching.

 

If you doing so, beware not to hit the salesforce limits (SQL lines, line scripts....)

 

mshelmanmshelman
Select c.FirstName,c.LastName,c.ReportsTo.FirstName, c.ReportsTo.LastName From Contact c

 

This was selected as the best answer
Janet GuoJanet Guo

I'm not sure that self-join logic makes sense. How can you find something using itself?

 

Would something like this be what you need:

 

SELECT Id, Employee_ID__c, FirstName, LastName
FROM   Contact
WHERE  ReportsTold = :findID

 (findId would be some Id that you would feed the query)

 

If you want to look for multiple Ids, you can use "WHERE ReportsTold IN :lstIds" where "lstIDs" is either a list of set or IDs.

Ganesh DhanavelGanesh Dhanavel

Thanks a lot mshelman, this solved my need.

should come out of my sql way of thinking... :)

 

Thanks

Ganesh

Ganesh DhanavelGanesh Dhanavel

Thanks Janet for getting back.

 

But the query you provided returns the data only matching the report to id, but that wasn't I was looking for.

I wanted to get employee id and his manager's employee id in one call.


 

Thanks

Ganesh

Ganesh DhanavelGanesh Dhanavel

Thanks liron169 for responding.

 

Earlier I was under the  same impression of 2 nested loops.

But we can take advantage of the salesforce id (parent-child relation ship) and get the results in a single query.

 

Pretty much in analogous to sql self/inner-join.

 

Thanks Again

Ganesh

Janet GuoJanet Guo

Ahh, I see. I didn't understand your original request. Glad you found the solution ^_^

mshelmanmshelman

Select c.ReportsTo.FirstName, c.ReportsTo.LastName, c.ReportsToId, c.LastName, c.FirstName From Contact c

 

This query answers the question: "who is a contact's manager? In the relationship ReportsTo on Contact, ReportsTo is the parent and Contact is the child. There can only be one ReportsTo per contact.

 

But if we want an answer to the question: "who are a contact's direct reports?"  then we might expect to see a reciprocal Child Relationship something like this:

 

Select c.LastName, c.FirstName, (select FirstName, LastName from <ContactDirectReports>) From Contact c

 

However, the corresponding Child Relationship to ReportsTo does not, in fact, exist. Possibly due to the recursive nature of the relationship

 

Just as an example, look at the relationship between Contacts and Contracts. A Contact is parent to Contract:

 

Select c.CustomerSigned.FirstName, c.CustomerSigned.LastName, c.CustomerSignedId, c.CustomerSignedDate From Contract c

 

where CustomerSigned refers to a Contact

 

and:

 

Select c.LastName, c.FirstName, (Select StartDate, EndDate From ContractsSigned) From Contact c

 

where ContractsSigned is the related list of Contracts for a Contact