Using a derived-column as key for a JOIN

Question

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

Please update your tag with the engine you're using (I.e. MySQL, SQL Server, etc.) Also, is custom_employee_id always padded with zeros or can it be an extra value I.e. 20009094 — griv, Sep 15 '22 at 06:04
Does this answer your question? [How do I use alias in where clause?](https://stackoverflow.com/questions/13031013/how-do-i-use-alias-in-where-clause) — Luuk, Sep 15 '22 at 06:57
Why not use INTEGER as datatype for the column `custom_employee_id`? that seems more appropriate. You can leave the formatting (adding of leading zeros) to you output tool. — Luuk, Sep 15 '22 at 07:05

score 0 · Answer 1 · answered Sep 15 '22 at 06:47

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.

score 0 · Answer 2 · answered Sep 15 '22 at 16:10

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

Using a derived-column as key for a JOIN

2 Answers2