how to get the same record multiple times in single SOQL query

Question

Account__c field is lookup in relationship__c object. I have to get all the accounts into a list. If the same is selected for 15 relationships, I have to get the same account 15 times in the final accList. But it was getting only 1 time instead of 15 times.

List<Id> idList = new List<Id>();
List<Relationship__c> vrList = [SELECT Id,Account__c FROM Relationship__c];
system.debug('SizeOfvrList'+vrList.size()); //here the Size is 15
for(Relationship__c v : vrList){
   idList.add(v.Account__C);
}
system.debug('SizeOfidlist'+idlist.size()); //idList size is 15
List<Account> accList = [SELECT Id,Name FROM Account WHERE ID IN : idList];
system.debug('SizeOfAccountLsit'+accList.size()); //here the size is 1

Answer 1

The following query is basically inner join which will give you single record for each account.

List<Account> accList = [SELECT Id,Name FROM Account WHERE ID IN : idList];

if you need to show all the account which is basically same record count of relationship records then you need to use Right Inner join for this:

List<Relationship__c> vrList = [SELECT Id,Account__c, Account__r.Name FROM Relationship__c];

Map<Id, Account> mapAccount = new Map<Id,Account>();
for(Relationship__c v : vrList){
   Account acct = new Account(Id=v.Account__c,Name=v.Account__r.Name);
   mapAccount.put(v.Id, acct);
}

This map will contain all the account along with relationshipId

mapAccount.values() will return all the Account records.

Refer A Deeper look at SOQL and Relationship Queries on Force.com