CODEWITHSUNDEEP
sqlsql-server
Ethan Bradberry
Ethan Bradberry

Reputation: 107

Conversion failed when converting the varchar value '"...."' to data type int

SELECT 
    tMI.MI
FROM
    tMI
LEFT JOIN 
    tCard ON tCard.CardNumber = tMI.MI
INNER JOIN
    TMIN on TMIN.T_ID = tMI.MI
WHERE
    tCard.CardNumber IS NULL
    AND tMI.MI IS NOT NULL

I am running the above code and without the inner join it runs fine. But once I add the inner join, it falls saying it cannot convert.

The data is the same minus maybe a few extras here and there in the table.

Any ideas how I can get around this error?

Upvotes: 1

Views: 7118

Answers (2)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Check all the columns mentioned in your ON condition are of same data type.

If you are storing the CardNumber as varchar (which is actually an integer value) and Mi as Integer, try to modify your on condition like below.

 ON CAST (tCard.CardNumber as INT) = tMI.MI

Upvotes: 1

Gokul Kumar
Gokul Kumar

Reputation: 9

I think your both tables columns are different datatype so you use following cast function in sql like 

SELECT 
    cast(tMI.MI as varchar)  as MI
FROM
    tMI
LEFT JOIN 
    tCard ON tCard.CardNumber = tMI.MI
INNER JOIN
    TMIN on TMIN.T_ID = tMI.MI
WHERE
    tCard.CardNumber IS NULL
    AND tMI.MI IS NOT NULL

Upvotes: 0

Related Questions