CODEWITHSUNDEEP
sqlsql-server-2008t-sql
meme
meme

Reputation: 113

T-SQL Comma delimited value from resultset to in clause in Subquery

I have an issue where in my data I will have a record returned where a column value will look like

-- query

Select col1 from myTable where id = 23

-- result of col1 111, 104, 34, 45

I want to feed these values to an in clause. So far I have tried:

-- Query 2 -- try 1

Select * from mytableTwo
where myfield in (
SELECT col1
from myTable where id = 23)

-- Query 2 -- try 2

Select * from mytableTwo
where myfield in (
SELECT '''' +  
Replace(col1, ',', ''',''') + ''''
from myTable where id = 23)

-- query 2 test -- This works and will return data, so I verify here that data exists

Select * from mytableTwo
where myfield in ('111', '104', '34', '45')

Why aren't query 2 try 1 or 2 working?

Upvotes: 0

Views: 595

Answers (3)

Gerardo Lima
Gerardo Lima

Reputation: 6703

I suppose col is a character type, whose result would be like like '111, 104, 34, 45'. If this is your situation, it's not the best of the world (denormalized database), but you can still relate these tables by using character operators like LIKE or CHARINDEX. The only gotcha is to convert the numeric column to character -- the default conversion between character and numeric is numeric and it will cause a conversion error.

Since @Gordon, responded using LIKE, I present a solution using CHARINDEX:

SELECT *
FROM mytableTwo tb2
WHERE EXISTS (
    SELECT 'x'
    FROM myTable tb1
    WHERE tb1.id = 23
    AND CHARINDEX(CONVERT(VARCHAR(20), tb2.myfield), tb1.col1) > 0
)

Upvotes: 0

Karthik D V
Karthik D V

Reputation: 926

If you observe closely, Query 2 -- try 1 & Query 2 -- try 2 are considered as single value.

like this :

WHERE myfield in ('111, 104, 34, 45')

which is not same as :

WHERE myfield in ('111', '104', '34', '45')

So, If you intend to filter myTable rows from MyTableTwo, you need to extract the values of fields column data to a table variable/table valued function and filter the data.

I have created a table valued function which takes comma seperated string and returns a table value. you can refer here T-SQL : Comma separated values to table

Final code to filter the data : 

DECLARE @filteredIds VARCHAR(100)

-- Get the filter data
SELECT @filteredIds = col1
FROM myTable WHERE id = 23

-- TODO : Get the script for [dbo].[GetDelimitedStringToTable] 
-- from the given link and execute before this
SELECT * 
FROM mytableTwo T
CROSS APPLY [dbo].[GetDelimitedStringToTable] ( @filteredIds, ',') F
WHERE T.myfield = F.Value

Please let me know If this helps you!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269473

You don't want an in clause. You want to use like:

select *
from myTableTwo t2
where exists (select 1
              from myTable t
              where id = 23 and
                    ', '+t.col1+', ' like '%, '+t2.myfield+', %' 
             );

This uses like for the comparison in the list. It uses a subquery for the value. You could also phrase this as a join by doing:

select t2.*
from myTableTwo t2 join
     myTable t
     on t.id = 23 and
        ', '+t.col1+', ' like '%, '+t2.myfield+', %';

However, this could multiply the number of rows in the output if there is more than one row with id = 23 in myTable.

Upvotes: 1

Related Questions