Help: Why IN-Operator with Select-Statement it doesnt work? But with given Values it works

Hello to all,

i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me.

I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418:

declare @.IDMint;

declare @.IDO varchar(8000);

set @.IDM= 3418;

set @.IDO='3430'

select*

from wtcomValidRelationshipsas A

where(A.IDMember= @.IDM)and( @.IDOin(3428, 3430, 3436, 3452, 3460, 3472, 3437, 3422, 3468, 3470, 3451, 3623, 3475, 3595, 3709, 3723, 3594, 3864, 3453, 4080))

but these numbers(3428, 3430, 3436, 3452, 3460, 3472, 3437, 3422, 3468, 3470, 3451, 3623, 3475, 3595, 3709, 3723, 3594, 3864, 3453, 4080)come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:

select*

from wtcomValidRelationshipsas A

where(A.IDMember= @.IDM)and( @.IDOin(select B.RelationshipIDsfrom wtcomValidRelationshipsas Bwhere B.IDMember= @.IDM))

I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks

I use MS SQL 2005 Server Management Stadio Express

Thanks a million and Best regards

Sha

What is the datatype of the column B.RelationshipIDs in the view?

|||

Please try:

First, removewhere B.IDMember= @.IDM from yuor statement, see you get some records or not. if you get, it means your B.IDMember= @.IDM criteria return no records.

If this no working, try

where(A.IDMember= @.IDM)andEXISTS(SELECT B.RelationshipIDsfrom wtcomValidRelationshipsas Bwhere B.IDMember= @.IDM ANDRelationshipIDs= @.IDO).

Sometimes, IN or NOT IN doesn't work but EXISTS does.