How do I make a select query for 2 tables in SOQL?

Question

I'm struggling with this and keep getting errors. I am trying to do:

SELECT
 table1.col1, table1.col2,
 table2.col1
FROM
  table1, table2
WHERE
 table1.col1 = table2.col2

I have tried:

SELECT 
BMCServiceDesk__Incident__c.BMCServiceDesk__Category_ID__c,
(SELECT User.department FROM User)
FROM
BMCServiceDesk__Incident__c
WHERE
BMCServiceDesk__Incident__c.BMCServiceDesk__clientEmail__c IN (SELECT User.email FROM User)

But got:

Didn't understand relationship 'User' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names

Hello, are you trying to query standard or custom objects? How are the objects related? I realize that you might not want to share the actual object names, but the code you provided is not enough information. — TechingCrewMatt
@TechingCrewMatt its an standard name actually... linking the user table data with incident data — Thabiso Motswagole

TechingCrewMatt TechingCrewMatt · Accepted Answer · 2020-03-07T21:35:21

Thanks for posting the query. The sub-query in your outermost SELECT clause needs to reference a relationship. For example, you can do the following on the Account and Contact objects to get a list of child Contacts for all Accounts:

SELECT Id, (SELECT Id FROM Contacts) FROM Account

I don't think you add a custom relationship to the User object so that sub-query won't work in your case. If you have a custom lookup on to the User object on BMCServiceDesk__Incident__c then you can traverse the parent relationship as follows:

SELECT Id, User__r.Department FROM BMCServiceDesk__Incident__c

Absent the custom relationship, I don't think you can do the inner join you are attempting in SOQL. You may need code the resembles the following to achieve it:

List<String> userEmails = new List<String>();
for(User user : [SELECT Id, Email FROM User WHERE IsActive = TRUE]){
    userEmails.add(user.Email);
}
List<BMCServiceDesk__Incident__c> incidents = new List<BMCServiceDesk__Incident__c>([
    SELECT  Id, BMCServiceDesk__Category_ID__c
    FROM    BMCServiceDesk__Incident__c
    WHERE   BMCServiceDesk__clientEmail__c IN :userEmails
]);
System.debug(incidents);

If this is something you need often, it might make sense to create a Visualforce Page or Lightning component to display the results. You could also add a trigger to the BMCServiceDesk__Incident__c object to populate a new field which stores the User Id if the email belongs to a valid User. That way, you can use standard reporting to display the results.

How do I make a select query for 2 tables in SOQL?

2 Answers