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
Marco RispoliMarco Rispoli 

Inner Joins in SOQL

I am totally new to SalesForce ... I literally just started playing with it 2 days ago. I signed up for a few classes (for next month) in the meanwhile I started playing with queries and I came across a situation that I can't seem to find a solution to: inner joins in SOQL.

Here's the scenario. 

I have this:
Account
and I have this:
AccountPartner
and I want to use this:
  
SELECT *
FROM Account AS ato, AccountPartner AS ap, Account AS afrom
WHERE ap.AccountToID = ato.AccountID
and ap.AccountFromID = afrom.AccountID;

To get this:
Join Result
How do I do it in SOQL?

PS: I dumped data in MS Access and ran the query I just pasted above ... That's where the join result set comes from.

Thank you!!
Swayam@SalesforceGuySwayam@SalesforceGuy
Hi Can You please share the exact object (Table Name) and relationship


--
Swayam
@Salesforceguy
Marco RispoliMarco Rispoli
The objects are Account and AccountPartner. I can query them in the developer console:
  
select AccountToId, AccountFromID FROM AccountPartner

or 
  
select Id, Name from Account

run just fine and return results (that's how was able to populate the tables in the Access database). 

However I can't find any information about the relationship between AccountPartner and Account ... 

Clearly it's there because the IDs are matching up.
Swayam@SalesforceGuySwayam@SalesforceGuy
Hi,


AccountPartner read-only object represents a partner relationship between two Account objects. It is automatically created when a Partnerobject is created for a partner relationship between two accounts.

I think, You won't be able to create joint Query. 

--
Regards,
Swayam
@Salesforceguy

 
Amit Chaudhary 8Amit Chaudhary 8
HI,

Please check below post. I hope that will help you
A Deeper look at SOQL and Relationship Queries on Force.com
https://developer.salesforce.com/page/A_Deeper_look_at_SOQL_and_Relationship_Queries_on_Force.com

Left Inner Join 
SELECT Name FROM Position_c WHERE Id IN
(SELECT Position__c FROM	Job_Application__c)
Right Inner Join 
SELECT Name,Position__r.Name,
FROM Job_Application__c
WHERE Position__r.Department__c = ‘Sales’

Please let us know if this will help you

Thanks
Amit Chaudhary


 
Pratyush NagPratyush Nag
select id,name from product2 where id in (select Product2id from opportunitylineitem )
this way we can write Inner join
Mark Leci 1Mark Leci 1
Since this post keeps showing up in my search results, I figured I would add some notes here for anyone that is trying to learn SOQL and may have previous experience with SQL. tldr - there is no way to do a true inner or outer join in SOQL. Keep reading by all means if you want to know more!
 
First, there is no such thing as a left or right inner join. By definition, an inner join returns records from both tables that have a matching value, so anything with 'left' or 'right' is not an inner join. This may help anyone who is trying to search for something similar with SOQL (but see below!)

Second, what is described above (in all the answers) is not an inner join at all. This is simply a subquery which limits the records in the main query. It's still useful but it's not the same at all. A join selects records from two objects based on a join condition. With a join (inner or outer), I could do something like this 

SELECT account.name, contact.name FROM account INNER JOIN contact on contact.accountID = account.ID

The above metaquery (which does not work in SF) selects a field from both the account and contact object in the same query. A subquery on the other hand can only select fields from the primary object:

SELECT name FROM contact WHERE accountid IN (SELECT id FROM account WHERE name = 'Test')

With this example (which does work in SF) we can select any fields from the contact object but nothing from the account, except for the accountId field, which actually exists on the contact object since its the foreign key. The number of records returned in both the above queries would be the same, however with a subquery of the above format, you cannot return any fields from the secondary query (the account object).

There is no way to do something like the above in Salesforce that I have found, i.e. a true join is not possible in SOQL. If you're using code such as Apex, you would have to first get the relevant account ID, then get all contacts with that account ID. If you're using developer console or something, you have to run a query first to get the unique id(s), then run a second query, like this 

SELECT id FROM account where name ='Test'
SELECT id FROM contact where accountid = [the ID from above]

As a side note, if you have to do something like this for multiple primary objects (as I do today which is what brought me here), I strongly recommend using the Chrome addon SFDC Dev Console Data Exporter (https://chrome.google.com/webstore/detail/sdfc-dev-console-data-exp/mfcddagpdnfokfnbnoeephmfoifgdbid). With it you can export the first object to csv, build a where clause, then run the query on the second object fairly quickly.