Can I LEFT JOIN between column types INT and VARCHAR?

This seems the best solution to me:

SELECT Students.PERSON_CODE, Students.PASSWORD
FROM Students
LEFT JOIN users 
ON cast(Students.PERSON_CODE as varchar(10)) = users.username
WHERE Students.PERSON_CODE > 0 

This way you don't need to check if username is an integer. You simply convert PERSON_CODE to a varchar(10) before comparing and problem is solved.


Try this:-

ON Students.PERSON_CODE = CASE 
                          When ISNUMERIC(users.username) = 1 THEN users.username
                          END

where Clause:

And ISNUMERIC(users.username) = 1