SOQL question - can you create VIEWS (like Oracle)? and then *JOIN* them together

Question

At the moment I have two main queries (College Application and Recommendations). Both "join" up to Contact for Student Name, Gender, etc. I'm doing this using SOQL in Workbench.

But I'd like to keep these as "clean" as possible, but then JOIN them together to create a larger dataset.

Is this possible? Is it possible to create a VIEW (like in Oracle) and then "JOIN" the two views?

*Having a hard time grasping how to pull data out of SF, for various analytical purposes.

Answer 1

There is no feature like VIEW in Salesforce. You must get the data from SOQL where you can perform Joins on multiple tables using relationship queries or you can use apex where you can store the result of SOQL in MAPs and then use the KeySet() from Map and perform another Query.

Option 1

List<CollegeApplication__c> appList = [SELECT Id, Name, (Select Id, Name FROM Recommendations__r) FROM CollegeApplication__c Where Name = 'XYZ' ]
    for (CollegeApplication__c app : appList){
        for(Recommendations__c recom : app.Recommendations__r){
            System.debug('Application: '+ app);
            System.debug('Recommendation: '+ recom);
        }
    }

Option 2

Map<Id,CollegeApplication__c> appMap = new Map<Id,CollegeApplication__c>([SELECT Id, Name FROM CollegeApplication__c Where Name = 'XYZ' ]);
List<Recommendations__c> recomList = [Select Id, Name FROM Recommendations__c WHERE Application__c IN : appMap.KeySet()];

for(Recommendations__c recom : recomList){
    System.debug('Recommendation: '+ recom);
    System.debug('Application: '+ appMap.get(recom.Application__c));
}