Inner Join Using SOQL Code for Data from Salesforce Objects

Question

I recently downloaded the Salesforce Input tool, and I am starting to play around with it. I can query data from one object, filter it, simple things. However, I want to perform an inner join on two objects (Account Object and User Object), and I am struggling with the SOQL code for it!

I want to bring in a few fields from Account, including OwnerId, and then bring in Name and Id from User, and then join on Account.OwnerId = User.Id.

I have tried using Account__c and User__r but I get errors telling me that "Account__c is not supported. If you are attempting to use a custom object, be sure to append the '__c' after the entity name.' Or I will get the same error just with 'be sure to append the '__r' after the customer relationship name'. Saw some stuff about Field Security issues, but unsure if that is what I am being bogged down by.

Here are some things that I have tried so far:

SELECT Account__c.OwnerId, User__r.Id
FROM Account__c

SELECT Account.OwnerId, User__r.Id
FROM Account

SELECT OwnerId
FROM Account
WHERE OwnerId IN (SELECT Id__c FROM User__c)

SELECT OwnerId
FROM Account
WHERE OwnerId IN (SELECT Id FROM User)

**This was a valid query, however, I want to actually bring in the Id and Name fields from User and this query only gives me OwnerId from Account!

Any insight would be greatly appreciated! Thank you!

apathetichell · Answer

SELECT Account.OwnerId
FROM Account
WHERE Account.OwnerId IN (SELECT User.Id FROM User)

does bring in fields from two different objects.

Account and User are two different objects.I do not believe SOQL supports standard joins on unrelated objects.

lindsaykeith · Answer

Thank you for the updated code! The last one is valid and works, but that will only give me Account.OwnerId in my output, whereas I need Account.OwnerId and also User.Name from the User Object. I am unsure how to bring in User.Name in addition to Account.OwnerId.

This is the query that works:

SELECT Account.OwnerId
FROM Account
WHERE Account.OwnerId IN (SELECT User.Id FROM User)

Any insight on bringing in the fields from two different objects? Also they are standard objects aka no parent/child relationship! Just wanted to clarify.

cjaneczko · Answer

If its anything like Oracle or Microsoft SQL I believe you need to include the table info with the select. See updates in Red.

SELECT Account__c.OwnerId, User__r.Id
FROM Account__c

SELECT Account.OwnerId, User__r.Id
FROM Account

SELECT Account.OwnerId
FROM Account
WHERE Account.OwnerId IN (SELECT User_c.Id__c FROM User__c)

SELECT Account.OwnerId
FROM Account
WHERE Account.OwnerId IN (SELECT User.Id FROM User)