Join tables on unfolded fields-CodePudding

I have one table with the following entries

TableA

id|companyCode|AbroadCode|CompName
 1|       1234|        99|BallComp

and another Table

TableB

id|compCode|EmpName
 1|  991234|Peter
 2|  991234|Thomas
 3|    1234|Frank
 4|    1234|Marcus

The problem is that for each company in Table A there is only one entry and if the company has a department abroad it gets a 99 in the column AbroadCode. In TableB there is an entry for each employee but separated (unfolded) depending on whether the person works for an aboard department or not. I know this should have been set up differently but I inherited it that way and now I cannot change the structure without much of work.

What I want to do is I want to join the tables. So that in the end I will get this:

id|compCode|EmpName|Compname
 1|  991234|Peter  |BallComp
 2|  991234|Thomas |BallComp
 3|    1234|Frank  |BallComp
 4|    1234|Marcus |BallComp

Could anyone help me with this query? I was able to solve it with two different queries (concat() on compcode and Abroadcode) but not in one query.

CodePudding user response：

You can try something along this:

Some sample data in a declared table variable

DECLARE @tblA TABLE(id INT,companyCode INT,AbroadCode INT,CompName VARCHAR(100));
INSERT INTO @tblA VALUES(1,1234,99,'BallComp')
                       ,(2,123,324,'Test') ;

DECLARE @tblB TABLE(id INT,compCode INT,EmpName VARCHAR(100));
INSERT INTO @tblB VALUES
 (1, 991234,'Peter')
,(2, 991234,'Thomas')
,(3,   1234,'Frank')
,(4,   1234,'Marcus')
,(5,    123,'Test1' )
,(6, 324123,'Test2' );

--the query

SELECT *
FROM @tblA a
INNER JOIN @tblB b ON b.compCode=a.companyCode 
                   OR b.compCode=CAST(CONCAT(a.AbroadCode,a.companyCode) AS INT);

The idea in short:

We join either against equal comp(any)Code ...
...or against the combination of AbroadCode and companyCode.