I'm trying to modify this SOQL statement in an APEX class:

SELECT Id, Name FROM Inventory__c WHERE Status__c = 'Active' ORDER BY Name

to filter the resulting list by record types from a parent object.  Inventory__c object is the child in a Master-Detail Relationship with a parent Transaction custom object that has two record types the names of which match a field value [Team__c] on the the Inventory object.  So, I want the WHERE clause to include filter to accomplish this intent:

SELECT Id, Name FROM Inventory__c 
WHERE (Inventory__c.Status__c) = 'Active' AND
(Transaction__r.RecordTypeName = Inventory__c.Team__c)
ORDER BY Name

Which I have tried several variations of, none of which work, all of which return errors indicating confusion in trying to resolve the relationship.  [The simple SELECT from Inventory works fine.]

Any suggestions.

I'm trying to modify this SOQL statement in an APEX class:

SELECT Id, Name FROM Inventory__c WHERE Status__c = 'Active' ORDER BY Name

to filter the resulting list by record types from a parent object.  Inventory__c object is the child in a Master-Detail Relationship with a parent Transaction custom object that has two record types the names of which match a field value [Team__c] on the the Inventory object.  So, I want the WHERE clause to include filter to accomplish this intent:

SELECT Id, Name FROM Inventory__c 
WHERE (Inventory__c.Status__c) = 'Active' AND
(Transaction__r.RecordTypeName = Inventory__c.Team__c)
ORDER BY Name

Which I have tried several variations of, none of which work, all of which return errors indicating confusion in trying to resolve the relationship.  [The simple SELECT from Inventory works fine.]

Any suggestions.