Is there a way of switching/changing the "ON" condition in a SQL query when the columns do-CodePudding

I have two tables (t1 and t2). I want to print/keep all records from t1 (we'll treat t1 as the left table). I want to perform a JOIN with t1 and t2 on two columns, but there's a problem. Table t1 consists of columns c1 and c2. Table t2 consists of columns c3, c4, and c5. I want to perform a JOIN between t1 and t2 on c1 (from t1) and c3 (from t2), but I also want to do a JOIN between t1 and t2 on c2 (from t1) and c4 (from t2) if records from c1 and c3 do not match.

Here are the two tables. Completely fictitious, but applicable to my real, work-related problem

The table below is what I want All the records/rows from t1 are printed.

I greatly appreciate anyone who comes forward with query solutions. With that being said, is there a way to solve this problem without UNION? Also, I am using SQL Server.

CodePudding user response：

Looking at the input and output data, I think you mean to compare c2 and c4 if c1 and c3 differ, not c1 and c2. I recreated the tables in sql and this code below gives the result you're looking for.

In that case you can just join and use an OR:

SELECT
    *
FROM
    t1
LEFT JOIN t2 ON
    t1.c1 = t2.c3
    OR t1.c2 = t2.c4;

CodePudding user response：

Are you sure you mean c1 and c2? They're columns from the same table. Looking at the schema, it also seems like c1 is a PK while c2 is an VARCHAR. But try this? You might need to convert Int to VARCHAR vice versa depending on your DBMS

select * from t1 JOIN t2 ON t1.c1 = t2.c3 OR (t1.c2 = t2.c4 AND t1.c1 <> t1.c2);