SQL join on multiple columns or on single calculated column-CodePudding

I'm migrating the backend a budget database from Access to SQL Server and I ran into an issue.

I have 2 tables (let's call them t1 and t2) that share many fields in common: Fund, Department, Object, Subcode, TrackingCode, Reserve, and FYEnd.

If I want to join the tables to find records where all 7 fields match, I can create an inner join using each field:

SELECT * 
FROM t1 
     INNER JOIN t2 
      ON  t1.Fund = t2.Fund 
      AND t1.Department = t2.Department 
      AND t1.Object = t2.Object 
      AND t1.Subcode = t2.Subcode 
      AND t1.TrackingCode = t2.TrackingCode 
      AND t1.Reserve = t2.Reserve 
      AND t1.FYEnd = t2.FYEnd;

This works, but it runs very slowly. When the backend was in Access, I was able to solve the problem by adding a calculated column to both tables. It basically, just concatenated the fields using "-" as a delimiter. The revised query is as follows:

SELECT *
FROM t1 INNER JOIN t2
        ON CalculatedColumn = CalculatedColumn

This looks cleaner and runs much faster. The problem is when I moved t1 and t2 to SQL Server, the same query gives me an error message:

I'm new to SQL Server. Can anyone explain what's going on here? Is there a setting I need to change for the calculated column?

CodePudding user response：

You have to calculate the columns before joining them as the ON clause can only access columns for the table.

It is no good to have two identical tables anyway so you should rethink your design completely.

SELECT t1a.*,t2a.*
FROM (SELECT CalculatedColumn, * FROM t1) t1a INNER JOIN (SELECT CalculatedColumn, * FROM  t2 ) t2a
        ON t1a.CalculatedColumn = t2a.CalculatedColumn

CodePudding user response：

Posting this an answer from my comment.

Usually, this is an issue with mismatched Data types between the two columns referenced. Check and make sure the data types of the two fields (CompositeID) are the same.