title: Can I LEFT JOIN between column types INT and VARCHAR?
body: <p>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´.</p>
  <p>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.</p>
  <p>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.</p>
  <p>Below is the query I have built so far - it's very simple:</p>
  <pre><code>SELECT Students.PERSON_CODE, Students.PASSWORD
  FROM Students
  LEFT JOIN users 
  ON Students.PERSON_CODE = users.username
  WHERE Students.PERSON_CODE &gt; '0' 
  AND users.username IS INTEGER &lt;- How do i do this. 
  </code></pre>
  <p>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.</p>
  <p><strong>Below was the full solution. Thank you Preveen.</strong></p>
  <pre><code>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 &gt; '0' 
  </code></pre>
  <p><strong>NEW UPDATES</strong></p>
  <p>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.</p>
  <pre><code>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))
  </code></pre>
  <p>In a way i need a full outter join essentially I think.</p>
  <pre><code>Table Students_________ Table users
  PERSON_CODE____________ username
  1______________________ 1
  2______________________ 2
  3______________________ 3
  4______________________
  5______________________
  6______________________
  7______________________
  8______________________
  </code></pre>
  <p>With this query I then want to display the results 4,5,6,7,8</p>