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
PERFECTIONISTPERFECTIONIST 

Lookup Relationship in SOQL

Dear All,

I would like to retrive data with the help of Lookup Relationship Field

1) I have Account Object---Account__c and created a Lookup field: Maincontact__c (lookup name is Maincontact__c)
2) Lookup value object is Contact : Contact__c and here we have child object for contact is Country: Country__c
3) here my question is i would like to Select the fields from Account, Contact and Country
 
Note: Account and Contact having only lookup relationship there is no parent and child relationship for Account and Contacts here

Can some one let me know how to retrive data based on my requirement.


Thanks,
Satis
 
Mudasir WaniMudasir Wani
Hi Satis,

You can retreive the data using relationship name fields.

Say the relationship name fields as as 
Account-- Contact --------Maincontact__r
Contact -- Country --------Country__r 

Then you need to make query on Country object and can do the following.
  
//Code to fetch data
Select name, Id, Contact__r.Name, Contact__r.Id, Contact__r.Account__r.Name, Contact__r.Account__r.id from Country__c
The above SOQL will fetch the name and Id from all three objects.
Just replace the relationship names

Hope this helps.!!!!!!



 
Shrikant BagalShrikant Bagal
Hello satis,

Can you please provide me relationship names of lookup fields.



 
PERFECTIONISTPERFECTIONIST
Hi Shrikant,

Lookup field Relationship Name is Maincontact__r for Contact
Object                                  Lookup Filed                       Relationship Name
Account__c                       MainContact __c                      Maincontact__r
Contact__c    
Child Object for Contact is  Country__c

There is no parent and child relationship between Account to Contact via lookup field relationship we are fetching data for contact so for so good but to fetch data of child object of Country i couldnt make it

Thanks if you can help me

Thanks,

 
Shrikant BagalShrikant Bagal
Please try 
  
SELECT Id, (SELECT Id From MainContact__c_FieldRelationshipName), (Select Id From SELECT Id From Contact__cFieldRelationShipName) From Contact

 
Mudasir WaniMudasir Wani
Try following 
//Code to fetch data
Select name, Id, MainContact__r.Name, MainContact__r.Id, MainContact__r.AccountId.Name, MainContact__r.AccountId.id from Country__c

 
PERFECTIONISTPERFECTIONIST
Dear all,

I tried but system is not able to understand the relationship why because there is no parent to child relationship for Account and Contact. through lookup relationship we could able fetch the data of contact so far so good but going forward to fetch data of country its not able to understand the relationship for system why becasue there is no direct  relationship for Country and Account.

Hope you got my point!

Thanks,
Mudasir WaniMudasir Wani
Hey Satis 

To fetch the data from account while quering from contact you can user the below code. 
SELECT Account.Name,Email,Id,Name FROM Contact
In above query account name will be fetched along with Contact emailId , Id and Name.
The relationship name is Account only.

Now if you have a relationship between Country and Contact as MainContact__r try the bwlow code 
  
//Code to fetch data
Select name, Id, MainContact__r.Name, MainContact__r.Id, MainContact__r.Account.Name, MainContact__r.Account.id from Country__c

It should work.

If that is still not working check if the below one is working first.
If this works then above one should work as well. 
//Code to fetch data
Select name, Id, MainContact__r.Name, MainContact__r.Id  from Country__c
Let me know in case of any issue.

Thanks.
 
Shrikant BagalShrikant Bagal
Hi Satis,

Have you try 
  
SELECT Id, (SELECT Id From MainContact__c_FieldChildRelationshipName), (Select Id From SELECT Id FromContact__cFieldChildRelationShipName) From Contact

 
PERFECTIONISTPERFECTIONIST
Hi All,

Above all scenarios doen't working i hope We can not fetch the data wtih the help of lookup field relationship to child object data.

Again I am explaining you scenario and try from your system as well if you have this kind of relationship in your organization.

1) Account:  is Master Object
Contact is an Object but there is no child relationship between Account and Contact

We have created lookup Field in Account object as Main contact in side the main contact lookup field we are showing the Contact Object.

2) Contact Object having child relationship with Country

Here Country is child of Contact

Contact to Country we have child relationship but Contact to Account having Lookup relationship.


hope you guys Clear, I have done all the queries which you guys provide and my self i hope you cannot call the data from look field to child relationship object data. this is what i got the conclusion.


Thanks for your suggestion.

Thanks,


 
Shrikant BagalShrikant Bagal
SELECT Id, (SELECT Id From MainContact__c_FieldChildRelationshipName), (Select Id From SELECT IdFromContact__cFieldChildRelationShipName) From Contact
PERFECTIONISTPERFECTIONIST
Hi Shrikant,

SELECT Id, (SELECT Id From MainContact__c_FieldChildRelationshipName),  Field childRelationshipName means what
 this is query will not pass 

(Select Id From SELECT IdFromContact__cFieldChildRelationShipName) From Contact

Where is the country and Account relatonship and why two times (Select Id From SELECT IdFromContact__c)

Have you tried before this kind of query in your organization?


Thanks,
Shrikant BagalShrikant Bagal
That was typo sorry, Please try:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT Id, (SELECT Id From ChildRelationshipWithAccount), (Select Id From ChildrelationshipWithCountry) From Contact

--------------------------------------------------------------------------------------------------------------------------------------------------

- ChildRelationshipWithAccount :-  Its the child relationship with Account and Contact which you will get on the "MainContact__c" field Defination page
- ChildrelationshipWithCountry:-  Its the child relationship with Conutry and Contact which you will get on the "Contact__c" field Defination page of  "country__c" Object.

FYI: When you create a lookup of any field its automatically create Parent-Child relationship.
 
Mudasir WaniMudasir Wani
Satis the query I have given you should work properly for you scnerio.
You just need to put the right relationship name that is it  
//Code to fetch data
Select name, Id, MainContact__r.Name, MainContact__r.Id, MainContact__r.Account.Name, MainContact__r.Account.id from Country__c

You can catch a screen show of scheema builder relationship and then I can assist you better.
Also Do following steps.

1. Go to country and find the Lookup field for Contact and see if I am using the correct one.
2. Account is already a parent of contact in salesforce so that should work as it is 

If below query is working then above one will be fine. 
//Code to fetch data
Select name, Id, MainContact__r.Name, MainContact__r.Id  from Country__c

Just see if the MainContact__r is the relaationship name and try the query in Developer Console first.
Shrikant BagalShrikant Bagal
Hello Mudasir,

I think you are get confussed between Account- Contact Standard relationship and Contact-Account relationship which is created by Satis.

In you query, you are using Standard relationship fo Account and Contact where account is Parent and Contact is Child but In Satis' sObject Schema he is created another relationship between Account and Conatct in which Contact is Parent and Account is Child.

Correct me if I am wrong anywhere.!!
Mudasir WaniMudasir Wani
This is not what salesforce will recommand.
Any ways he can use your query with little modification as.
  
SELECT Id, (SELECT Id,name From MainContacts__r), (Select Id From yourCountryRelationpname) From Contact
Note that I have used the plural label that is instead of "MainContact__r" ---- I have used "MainContacts__r" note the addition of "S" Just add the same relationship name of Country as you are done !!!!!!!!!!!!!!!!!!!!!!11

Thanks Shrikant for mentionng this.


 
PERFECTIONISTPERFECTIONIST
Dear Shrikant\Mudasir,

Good Morning,

Hope you are doing well!

Okey I got your confusion Point here, forget about account and contact relationship in salesforce will take an new example with our names.

Below is the example:

1) Satis__c is an Object
2)Shrikant__c is also an another Object
3) Mudasir__c is also an Object
--------------------

Step1: Satis and Shrikant does not have any parent and child relationship
due to which i have created lookup field in Satis Object for to communicate Shrikant
l have given a lookup field name as "Mainshrikant__c" for lookup relationship name is Mainshrikant__r
Step2: Shrikant and Mudasir having Parent Child relationship,  Mudasir is a child object under the Shrikat__c object.

Here my concern is i would like to get data from Satis table, Shrikant table and Mudasir table.

How do you want me to query for all the three tables in single query.


Note: Remember there is no parent and child relationship for satis and shrikant we have created lookup field relationship.

Thanks for your speedy response.

Thanks,



 
Shrikant BagalShrikant Bagal
Please try 

SELECT Id, (SELECT Id From Mainshrikant__r), (Select Id From childrelationshipName of Shrikant and Mudasir) From Shrikant__c 

 
PERFECTIONISTPERFECTIONIST
Dear Shrikant,

i am finding the below error

INVALID_TYPE: 
SELECT Id,(SELECT Id From MainShrikant__r),(Select Id From
^
ERROR at Row:1:Column:27
Didn't understand relationship 'MainShrikant__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.


Thanks,
 
PERFECTIONISTPERFECTIONIST
Dear Shrikant,

Above results from  WorkBench and the below error from Developer console


SELECT Id,(SELECT Id From MainShrikant__r),(Select Id From
                          ^
ERROR at Row:1:Column:27 Didn't understand relationship '
MainShrikant__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.


Thanks,
Shrikant BagalShrikant Bagal
Have you create MainShrikant__c field on object?
Could you please share defination page of this field not Object "MainShrikant__c" Field ?

Thanks!
PERFECTIONISTPERFECTIONIST
User-added image

Please find the above definition page
Shrikant BagalShrikant Bagal
please try 


SELECT Id, (SELECT Id From Satis__r), (Select Id From Mudasirs__r) From Srikant__c 


 
PERFECTIONISTPERFECTIONIST
Dear Shrikant,
So far so good we can select the data from two table but i need to put where condition for specific field in Satis object and specific field in Shrikant object based on that i need to pull the data for all the 3 tables.

Thanks,,
Shrikant BagalShrikant Bagal
Hello Satis,
---------------------------------------------------------------------------------------------------------
SELECT Id, (SELECT Id From Satis__r WHERE YOUR_CONDITION), (Select Id From Mudasirs__r) From Srikant__c  WHERE YOUR_CONDITION
-------------------------------------------------------------------------------------------------
If its helps, please mark as best answer so it will help to other who will serve same problem.
​Thanks! 
PERFECTIONISTPERFECTIONIST
I tried with this query before i posting you, but its filtering data for seperate for each table and showing for satis seprately
and also filtering data for Mudasir and Srikant

i tried to put where condition with AND operator for the both buts its giving an error

I need a combined related data of all three 3 tables.


 
Shrikant BagalShrikant Bagal
Could you please post your query with Where condition?
PERFECTIONISTPERFECTIONIST
Please find the below query

SELECT Id, Family__c,(SELECT Accounts__r.AccountNumber From Satis__r Where Accounts__r.AccountNumber = '123456'),(Select Platform__c From Mudasirs__r) From Shrikant__c Where Family__c ='Heaven' 
Shrikant BagalShrikant Bagal
try


SELECT Id, Family__c,(SELECT Accounts__r.AccountNumber From Satis__r Where Accounts__r.AccountNumber = '123456' AND Main_srikant__r.Family__c ='Heaven'),(Select Platform__c From Mudasirs__r) From Shrikant__c Where Family__c ='Heaven' 
 
PERFECTIONISTPERFECTIONIST
No luck! We changed the query but result is showing same as prior query :)
PERFECTIONISTPERFECTIONIST
as per above query first are selecting Family__c from Shrikant__c where Family__c = 'Heaven'

so its fetching all the family which is related to Heaven and on top of that its showing the account number which is having family and Platform.
Example: AccountNumber='123456' is having only one family with the name of Heaven and Platforms so for so good its fetching and also its searching all the family which i name Heaven.

here we need to restrict to show all the family with the name of Heaven. we have to show only with this account number associaed family with the name of Heaven

Thanks,

 
Shrikant BagalShrikant Bagal
Is there any relationship between Account and Srikanth__c?
 
PERFECTIONISTPERFECTIONIST
Noooooooooooo Broooooooooo
Shrikant BagalShrikant Bagal
Finally we can do following think:
-----------------------------------------------------------------------------------------------------------------
List<Shrikant__c> lstShrikant = [
SELECT Id, Family__c,(SELECT Accounts__r.AccountNumber From Satis__r Where Accounts__r.AccountNumber = '123456' AND Main_srikant__r.Family__c ='Heaven'),(Select Platform__c From Mudasirs__r) From Shrikant__c Where Family__c ='Heaven' 
];

List<Shrikant__c> lstRequiredRecords = new List<Shrikant__c>();

for(Shrikant__c record :  lstShrikant){
    if(record.Satis__r != null && record.Satis__r.size() > 0){
              lstRequiredRecords.add(record);
     }
}

// Finally you get required result . in "lstRequiredRecords"

--------------------------------------------------------------------------------------------------------------------

let me know if helps!!!!!


Thanks!
PERFECTIONISTPERFECTIONIST
can run this query  in workbench, I need soql query Only :)
Shrikant BagalShrikant Bagal
lets try:

--------------------------------------------------------------------------------------------------------------------

SELECT Main_srikant__c, Main_srikant__r.Family__c,Accounts__r.AccountNumber ,(Select Platform__c From Main_srikant__r.Mudasirs__r) From Main_srikanth__c Where Accounts__r.AccountNumber = '123456' AND Main_srikant__r.Family__c ='Heaven'

--------------------------------------------------------------------------------------------------------------------

FYI:
I never try, just let me know what happens with this query.


 
Shrikant BagalShrikant Bagal
Is it Working? 
PERFECTIONISTPERFECTIONIST
No Shrikant,

I am finding below error!
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INVALID_TYPE: 

Main_srikant__r.Mudasirs__r) From
Main_srikanth__c Where Accounts__r.AccountNumber = '123456'
^
ERROR at Row:1:Column:167
sObject type 'Main_srikanth__c' is not supported. If you are attempting to use a custom object, be sure to append the '__c' after the entity name. Please reference your WSDL or the describe call for the appropriate names.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Thanks,
prayank sahu 7prayank sahu 7
Please go through the below links for more detail about relationship in salesforce

https://youtu.be/9ltnHYQ1tqE
https://youtu.be/3ULokr4yaEE