Using a derived-column as key for a JOIN-CodePudding

Given

CREATE TABLE Person (
  id bigint,
  first_name nvarchar(60),
  last_name nvarchar(60),
  custom_employee_id nvarchar(60)
);

CREATE TABLE User (
  id bigint,
  username nvarchar(60),
  department nvarchar(60)
);

INSERT INTO Person ( id,first_name,last_name,custom_employee_id)
VALUES (1,'steve','rogers','00009094');

INSERT INTO User ( id,username,department)
VALUES( 23,'9094','accounting');

I want to join on Person.custom_employee_id and User.username. Both are nvarchars so this should be cake but custom_employee_id has embedded leading zeros and also nulls to contend with.

SELECT A.id, A.first_name, A.last_name, A.custom_employee_id
     , CAST( CAST( A.custom_employee_id) AS bigint) AS nvarchar) AS trythis
FROM Person A
WHERE ISDECIMAL( A.custom_employee_id) =1

seems to get me close with

1,'steve','rogers','00009094','9094'

so trying

SELECT A.id, A.first_name, A.last_name, A.custom_employee_id
     , CAST( CAST( A.custom_employee_id) AS bigint) AS nvarchar) AS trythis
     , B.department
FROM Person A
LEFT JOIN USER B ON B.username = 'trythis'
WHERE ISDECIMAL( A.custom_employee_id) =1

does execute but yields

1,'steve','rogers','00009094','9094',NULL

instead of the hoped-for

1,'steve','rogers','00009094','9094','accounting'

So how must I adjust my query?

TIA,

Still-learning Steve

CodePudding user response：

So two possible answers here but keep in mind neither are optimal as you never want to do operations in your match as this is not SARGable. So long as the tables remain relatively small this shouldn't a noticeable performance impact.

SELECT 
    * 
FROM @Person P 
JOIN @User U 
    ON CONVERT(int ,U.[username]) = CONVERT(int ,P.[custom_employee_id]) 
    
SELECT 
    * 
FROM @Person P 
JOIN @User U 
    ON RIGHT('00000000'   U.[username], 8) = P.[custom_employee_id]

On the second query "RIGHT('00000000' U.[username], 8)" is assuming that "P.[custom_employee_id]" is always a length of 8 characters.

CodePudding user response：

Turns out just replicating the double-cast and switching from LEFT JOIN to JOIN did the trick

SELECT A.id, A.first_name, A.last_name, A.custom_employee_id
     , CAST( CAST( A.custom_employee_id) AS bigint) AS nvarchar) AS trythis
     , B.department
FROM Person A
JOIN USER B ON B.username = CAST( CAST( A.custom_employee_id) AS bigint) AS nvarchar)
WHERE ISDECIMAL( A.custom_employee_id) = 1

Thanks to all that replied!

CASE CLOSED

Still-learning Steve