CODEWITHSUNDEEP
sqlsql-serverjoin
Erma
Erma

Reputation: 397

SQL Server - Join only when the condition is number

I have a table EmployeeLeave

  CREATE TABLE EmployeeLeave (
  EmployeeId INT,
  LeaveType VARCHAR()
  );

LeaveType can have numbers as well as string. 

  CREATE TABLE MST_LeaveReason (
  ReasonId INT,
  Reason VARCHAR()
  );

  SELECT * FROM EmployeeLeave EL
  LEFT JOIN MST_LeaveReason LR ON LR.ReasonId = EL.LeaveType

Above query will not work as LeaveType is not always number. I want this join to work only if the LeaveType is number. How can i implement it. Please help.

Thanks in advance.

Upvotes: 1

Views: 1078

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Use try_convert():

SELECT *
FROM EmployeeLeave EL LEFT JOIN
     MST_LeaveReason LR
     ON LR.ReasonId = TRY_CONVERT(INT, EL.LeaveType);

You can also do this in the other direction:

SELECT *
FROM EmployeeLeave EL LEFT JOIN
     MST_LeaveReason LR
     ON CONVERT(VARCHAR(255), LR.ReasonId) = EL.LeaveType;

Then, work on fixing the data model. JOIN keys should be of the same type. Such conversions have a drastic affect on performance.

Upvotes: 4

Related Questions