+ Start a Discussion
D A LynskeyD A Lynskey 

SOQL Syntax for child to parent relationship reference

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)


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.
Azhar Aziz GAzhar Aziz G
SELECT Id, Name FROM Inventory__c 
WHERE Status__c = 'Active' AND
Team__c = Transaction__r.RecordType.Name
Try above SOQL.
D A LynskeyD A Lynskey

I can do this:

SELECT Id, Name FROM Inventory__c 
WHERE (Inventory__c.Status__c) = 'Active' AND
(Inventory__c.Team__c = 'AM')

and it works...

I can do:

Select RecordType.Name from Transaction__c

and it works, returning the value 'AM' 

But, when I put the two together an try to use the relationship as above, I get "Unknown error parsing query", so the unrelated parts work just fine [and THANK YOU for the "RecordType.Name" correction], but they don't like playing together.