Q:

How can I compare a field to another field in a different list through a SOQL WHERE clause?

I am building a Visualforce PDF page with a controller extension to display details from the parent object and child object in the same table. The table's first two columns reference the records list from the extension to provide high level information and then the third column references the records2 list from the extension to retrieve all of the activity records related to the records list.
The architecture is the Visualforce page references the standard controller for the SalesPlan__c object and also references a controller extension. There is a child object of SalesPlan__c called SalesPlanGoal__c where high level objectives are captured. Then there is a child of SalesPlanGoal__c called SalesPlanActivity__c where actionable tasks are noted. SMART_Goal__c is the name of the lookup field for SalesPlanGoal__c object on SalesPlanActivity__c.
I have no problem displaying the Name and Description of the SalesPlanGoal__c records as separate rows in a table (i.e. Initiative and SMART Goals columns in image below). I am struggling with filtering the SalesPlanActivity__c records to show in the third column (i.e. Benchmark Activities) with their corresponding SalesPlanGoal__c record. I am currently displaying all SalesPlanActivity__c records for the SalesPlan__c on each SalesPlanGoal__c instead of having all the SalesPlanActivity__c records specific to a SalesPlanGoal__c.

Below is my controller extension:
public with sharing class SalesPlanPDFExtension{

public List<SalesPlanGoal__c> records = new List<SalesPlanGoal__c>();
public List<SalesPlanActivity__c> records2 = new List<SalesPlanActivity__c>();

public List<SalesPlanGoal__c> getrecords() {    
    return records;
}
public List<SalesPlanActivity__c> getrecords2() {    
    return records2;
}
    /*Constructor*/
    public void SalesPlanPDFExtension() {  
    }/*End Constructor*/

    /*Constructor*/
    public SalesPlanPDFExtension(ApexPages.StandardController controller) {
        records = [SELECT Id,Name,Description__c,Sales_Plan__c,SMART_Goal__c,Key_Initiative__c
                        /*,
                        (SELECT Id,Name,Account__c,Contact__c,Key_Initiative__c,Sales_Plan__c,SMART_Goal__c,Status__c,Subject__c 
                        FROM BenchmarkActivities__r)
                        */
                   FROM SalesPlanGoal__c 
                   WHERE Sales_Plan__c=:ApexPages.currentPage().getParameters().get('id')
                   ORDER BY Name ASC
                  ];
        records2 = [SELECT Id,Name,Account__c,Contact__c,Due_Date__c,Key_Initiative__c,Sales_Plan__c,SMART_Goal__c,SMART_Goal_Objective__c,Status__c,Subject__c
                    FROM SalesPlanActivity__c 
                    WHERE SMART_Goal__c IN :records
                    //AND Key_Initiative__c = 'Exteriors'
                    ORDER BY Key_Initiative__c ASC
                   ]; 
    }
    /*End Constructor*/
   }

I can get the SalesPlanActivity__c to filter if I add a text literal match to the WHERE statement in records2 such as Key_Initiative__c = 'Exteriors' but I need it do be more dynamic than that because then the SalesPlanActivity__c records for a different initiative/goal type only shows the SalesPlanGoal__c Exteriors SalesPlanActivity__c records. I would ideally like something like WHERE SMART_Goal__c IN :records AND Key_Initiative__c = records.Name.
I figure I need something like records[i].name where i is the iteration of the row, but I have no idea how to accomplish this.
Any ideas on how to solve this problem?
Will you please help?

A:

If you are looking to do a collection comparison, it is fairly straightforward. Use the IN operator as you do with your Id check, and build up a collection of the values you are looking for. It is a little unusual because you can check if an Id is in a collection of SObject, normally you need your collection to be of the same type as the field you are trying to filter.
Set<String> goalNames = new Set<String>();
Set<Id> goalIds = new Set<Id>();
// the second set is not necessary but would be if you wanted to pull a different Id
for (SalesPlanGoal__c goal : goals)
{
    goalNames.add(goal.Name);
    goalIds.add(goal.Id);
}

List<SalesPlanActivity__c> activities = [
    SELECT Id FROM SalesPlanActivity__c
    WHERE SMART_Goal__c IN :goalIds
    AND Key_Initiative__c IN :goalNames
];

