+ Start a Discussion

Building SOQL relationship query between parent/child

I am building an advanced search page using VisualForce for objects in our SalesForce implementation.  Because of the multiple variables and situations and the limitations of Apex and SOQL in general, I have chosen to build dynamic SOQL to accomplish my goals.  This is all working fine but I am having some issues with a relationship query and I'm having a bit of trouble wrapping my head around the proper resolution.  I am building a SOQL query to search the "Account" standard object but in our SalesForce we have a "Location" custom object that holds geographic location for accounts.  It is a master/child relationship so an account(parent) can have multiple locations(children).  If someone enters a city, the SOQL query WHERE must check againt a field in Location and not Account.  An example query is as follows:


SELECT Id, Account_ID__c, Name FROM Account WHERE Name = :accountName AND Locations__r.City__c = :accountCity ORDER BY Account_ID__c DESC


This does not work for me and does not even make sense to me that it would work.  In SQL this would be accomplished with a join between account and location and then you could check against the city, but there are no joins in SOQL.  The above query needs to return all accounts where name = "variable account name" and location.city = "variable account city".  A single account can have multiple cities though as each location for a particular account is a different address.  I probably did a poor job of explaining this, but if anyone can give me some insight as to how to get this done or let me know what I'm doing incorrectly, it would help quite a bit.




You would need to use a semi-join query for this, something like


select id, name from account where name := acctName and id in (select account_id__C from location__c where city__c = :acctCity ) order by id desc 


When i use OR concondition for the query which you mentione then it is throwing error saying that semi joins dont accept 'OR' condition.


Can you suggest what should i do to get the result.