You need to sign in to do that
Don't have an account?
Ganesh 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
All Answers
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....)
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:
(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.
Thanks a lot mshelman, this solved my need.
should come out of my sql way of thinking... :)
Thanks
Ganesh
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
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
Ahh, I see. I didn't understand your original request. Glad you found the solution ^_^
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