function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
VikasVikas 

Does SFDC has a constraint that we can not execute a join query?

I am fetching the data using Java, Does SFDC has a constraint that we can not execute a join query? Can retrieve data of more than one entity in a single query ? e.g. I need to fetch contacts with acocunt names. (not acocunt IDs).

Please help

Thanks,

HaroldHHaroldH
This is correct. We all face this current limitation. SOQL does not provide a means for querying more than one table (object) at a time.

I've found that judicious use of multiple queries, followed by joining on the client (your application server) can provide the desired functionality. It's not optimum, but it works.
AmarAmar
Hi HaroldH,

Can you please tell us a bit more about "use of multiple queries, followed by joining on the client"?

I am using Tomcat Server, do you have any suggestion that how to implement join feature with this server.

How preferable it could be to dump the SFDC Entity in "Java Collection Framework" objects and retrieve the required values on the fly.


Thanks,
Amar
HaroldHHaroldH
Keep in mind that SOQL does not provide any JOIN syntax, so one way to handle this limitation is to make multiple queries and "join" them in your own application. That said, here's a not-quite-pseudocode example of the functional equivalent to this join statement:

SELECT Account.Name FROM Account JOIN Contact ON Account.ContactId = Contact.Id WHERE Contact.Email = "email@address.com"

Here we want to get the name of the account which owns the contact with a specified email address. Since we can't perform the join, we can execute two queries.

SELECT AccountId, Id FROM Contact WHERE Email = "email@address.com"

Let's pretend, for the sake of example, that this returns a list of two elements ( (AccountId='abc1111', Id='xyz333'), (AccountId='abc2222', Id='xyz444') ).

Next, execute a query against the Account table with the AccountId fields from your list. (It would be more efficient, and faster, to use the retrieve method since we have the list of Account Id fields, but this example is simple for the purpose of illustration.)

SELECT Id, Name FROM Account WHERE Id = 'abc1111' OR Id = 'abc2222'

Now you have a second list of two elements ( (Id='aaa111', Name='Account One'), (Id='abc2222', Name='Account Two') ).

Join these two lists using the AccountId field of the first list, with the Id field of the second list. Now you have a third two element list.

This is just an example to illustrate the idea. As has been said here and elsewhere, it would be better to use a retrieve() call to retrieve the Account objects for the set of Ids.

As for using the "Java Collection Framework", I can't speak to that approach. Sounds reasonable. This is just another possible approach.

Message Edited by HaroldH on 01-03-2005 02:02 PM