Can I LEFT JOIN between column types INT and VARCHAR?

Question

I have been given the task to look into a issue with a MSSQL Query and I'm not SQL pro! It's not my forte´.

I have 2 tables in 2 different databases; ignore table references, as I have stripped them out of this query. My issue is PERSON_CODE is a integer value in one table and VARCHAR in another, I will be running this query and parsing the data directly into the Users table whilst hashing passwords in PHP.

However, because some of the fields in the Users table are character names like Jane, John and all values in the Students are integer only I receive a error because of data types.

Below is the query I have built so far - it's very simple:

SELECT Students.PERSON_CODE, Students.PASSWORD
FROM Students
LEFT JOIN users 
ON Students.PERSON_CODE = users.username
WHERE Students.PERSON_CODE > '0' 
AND users.username IS INTEGER <- How do i do this.

I need to know if there is a way of ignoring the fields which are not integers in the Users table so I can get only integer results from the Students table.

Below was the full solution. Thank you Preveen.

SELECT Students.PERSON_CODE, Students.PASSWORD
FROM Students
LEFT JOIN users 
ON Students.PERSON_CODE = CASE 
                          When ISNUMERIC(users.username) = 1 THEN users.username
                          END
WHERE Students.PERSON_CODE > '0'

NEW UPDATES

I have made some changes because I want the query to show only rows which are not in both tables using PERSON_COde/username as the identifier.

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

In a way i need a full outter join essentially I think.

Table Students_________ Table users
PERSON_CODE____________ username
1______________________ 1
2______________________ 2
3______________________ 3
4______________________
5______________________
6______________________
7______________________
8______________________

With this query I then want to display the results 4,5,6,7,8

Thanks for the edit Bhadra, sorry I forgot to change the SQL after removing a comment on the second from last line!! doh. — Steve Church, Jan 23 '14 at 08:43
is your question answered here? http://stackoverflow.com/questions/5064977/detect-if-value-is-number-in-mysql — Bokw, Jan 23 '14 at 08:47
You should change `PERSON_CODE > '0'` into `PERSON_CODE > 0` because it's an integer — NickyvV, Jan 23 '14 at 08:56
you can drop the where clause if you change it into a regular join instead of a left join in your solution — t-clausen.dk, Jan 24 '14 at 06:22

t-clausen.dk · Accepted Answer · 2017-10-19T12:56:01.137

21

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.

edited Oct 19 '17 at 12:56

answered Jan 23 '14 at 09:25

t-clausen.dk

43,517
12
59
92

@praveen I was working on a method much like yours when suddenly it became clear – t-clausen.dk Jan 23 '14 at 09:28
Right you both are, I ran in to issues with PRaveen's solution as it was returning the full set every time. – Steve Church Jan 23 '14 at 10:17
Ok I now have this: - damn I cant put code in this message. one momnent – Steve Church Jan 23 '14 at 10:20

praveen · Answer 2 · 2014-01-23T09:03:49.153

2

Try this:-

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

where Clause:

And ISNUMERIC(users.username) = 1

edited Jan 23 '14 at 09:03

answered Jan 23 '14 at 08:46

praveen

12,083
1
41
49

true, but he uses a LEFT JOIN with a WHERE clause, that gives another outcome as your code above does. – NickyvV Jan 23 '14 at 08:58
NickyvW I am. I will try this Praveen – Steve Church Jan 23 '14 at 09:10
Praveen you are a legend... It works. I will post the finished result below. – Steve Church Jan 23 '14 at 09:13
@SteveChurch : Just to let you know there are some issues with Isumeric function as it returns `1` even for `,`,`.`,`-` . So in the `When` clause you can check for the above strings too – praveen Jan 23 '14 at 09:18

Irawan Soetomo · Answer 3 · 2019-11-30T16:15:50.983

1

Sometimes using ISNUMERIC() won't work too well, as comma and decimal point would also be considered as numeric. I prefer to use TRY_CONVERT(int, a.var_key) = b.int_key, as TRY_CONVERT() will give NULL to ignore the non-integer values needed for the JOIN to work.

edited Nov 30 '19 at 16:15

answered Nov 30 '19 at 16:08

Irawan Soetomo

1,315
14
35

Sundar · Answer 4 · 2014-01-23T09:23:07.853

0

You can use like this also, optimization using derived table join will avoid full table scan in sql join process in a bigger table.

SELECT Students.PERSON_CODE, Students.PASSWORD
FROM Students
LEFT JOIN
(SELECT users.username FROM users 
WHERE ISNUMERIC(users.username)= 1) As Myusers
ON Students.PERSON_CODE = Myusers.username
WHERE Students.PERSON_CODE > 0

edited Jan 23 '14 at 09:23

answered Jan 23 '14 at 08:52

Sundar

4,580
6
35
61

Can I LEFT JOIN between column types INT and VARCHAR?

4 Answers4