[SalesForce] query flat list of records including parent and child fields

I have two custom objects in a master detail relationship where Proposal is parent and Question is child.

I need to create a page where all the questions in the DB are shown along with certain data elements from parent object and create a table e.g.:

Proposal Name     Proposal Type  Question Name Question Text

Normally in an SQL you can get that result with a query like below:

Select proposal_name, proposal_type, question_name, question_text 
from proposal, question 
where question.proposal_id = proposal.id

But I am not able to get the similar construct that I can use in APEX controller to fetch the desired set of data?

So far I have tried following combinations

[Select Proposals__r.Name, Proposals__r.Client_Name__c, Proposals__r.proposal_type__c, Question__c from Question__c];

Error:

Didn't understand relationship 'Proposals__r' in field path. If you
are attempting to use a custom relationship, be sure to append the
'__r' after the custom relationship name.

2nd Option:

[Select id, name, Client_Name__c, proposal_type__c, (select question__c from Questions__r) from proposals__c];

Result
Query worked but not able to access questions data in visualforce page to display.

Best Answer

There are 2 ways in which you can wirte SOQL queries.

1) We can get child object fields/ records by using inner query like this :

 [Select, Id, Name, Client_Name__c, Proposal_Type__c, (Select Id from Questions__r) from proposal__c];

2) OR

 [Select Question__c, Proposal__r.Name, Proposal__r.Client_Name__c, Proposal__r.proposal_type__c from Question__c];

// we dont use plural form of parent object while quering on the child object.

This will solve your problem. If you still have any issues regarding SOQL queries, follow this link https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm#sforce_api_calls_soql_relationships_query_using

Related Topic