I have the following query:
FROM tbl_ara ara
WHERE id in (
SELECT ',' + cast(id as varchar(8000))
WHERE ct_id = 2
FOR XML PATH('')
), 1, 1, ''))
Msg 245, Level 16, State 1, Line 11 Conversion failed when converting
the nvarchar value
to data type int.
STUFF in your case returns character data. From the documentation (https://msdn.microsoft.com/en-us/library/ms188043.aspx):
Returns character data if character_expression is one of the supported character data types. Returns binary data if character_expression is one of the supported binary data types.
So you do not get a list of IDs but rather a string containing numbers and commas.
Is there a reason for not using the IDs directly?
SELECT [ara].[req_id] FROM [tbl_ara] [ara] WHERE [id] IN ( SELECT [id] FROM [contact] WHERE [ct_id] = 2 )
EDIT: Of course the
JOIN solution by @gofr1 is a pretty good option, too.