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

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

Solution

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.