SOQL from a junction record?

Question

Hopefully this is simple to everyone with a bit more experience. I am trying to retrieve related records with a query. The objects are related via a Junction Object. I've done quite a bit of searching but haven't found a method to do this

The three objects are: Account, A junction object called CovenantJunction where - Account is master, Covenant is master (LLC_BI__Account_Covenant__c), and Covenant (LLC_BI__Covenant2__c)

How can I get all of the related Covenant records if I have the Account Id?

Answer 1

Here is one way:

Id accountId = ...;

Covenant__c[] covenants = [
        select Id, Name, ...
        from Covenant__c
        where Id in (
                select Covenant__c
                from CovenantJunction__c
                where Account__c = :accountId
        )];

Refer to the A Deeper look at SOQL and Relationship Queries on Force.com for more explanation.

Answer 2

Assuming the fields api name on CovenantJunction is Covenant__c and Account.You can use a query like this:

select Covenant__r.name from CovenantJunction where Account='a00P0000003IeLt'

Here the last part is the id. If you have it in a variable named accountid ,then you can use:

select Covenant__r.name from CovenantJunction where Account=:accountid

If there are more than one covenant__c record then you can use a list to store the CovenantJunction and iterate the list to get the covenant__c.

List<CovenantJunction> s =[select id,covenant__r.name from CovenantJunction where Account=:accountid];