SQL : Error converting data type varchar to numeric

Question

I have met a very strange problem. There are two sql queries:

Q1:

SELECT *
FROM tbl_1
WHERE ID IN (SELECT TargetID
             FROM tbl_2 
             WHERE PeopleID = 'AAA') 
AND ID = 49

Q2:

SELECT *
FROM tbl_1
WHERE ID IN (SELECT TargetID
             FROM tbl_2 
             WHERE PeopleID = 'BBB') 
AND ID = 49

We could find these two queries are identical except for PeopleID. But their result were very difference. For Q1, we got "Error converting data type varchar to numeric"; And for Q2, it ran well. I was confused!

There are some infomation that may be useful.

tbl_1  ID       numeric(18,0) not null

tbl_2  TargetID varchar(50)   not null

and every TargetID in Q1 or Q2 could pass the IsNumeric test (namely, IsNumeric(TargetID) = 1)

another information: if we comment the line AND ID = 49 and every query run well

thx for your help

Answer 1

i would try doing something like this instead:

SELECT *
FROM tbl_1
WHERE convert(varchar(50), ID) IN (SELECT TargetID
             FROM tbl_2 
             WHERE PeopleID = 'BBB') 
AND ID = 49

since the ID is converted to a varchar, it should work ok.

Answer 2

How about using EXISTS?

SELECT *
FROM tbl_1
WHERE EXISTS (SELECT *
             FROM tbl_2 
             WHERE PeopleID = 'BBB'
             AND tbl_2.TargetID = tbl_1.ID) 
AND ID = 49