CODEWITHSUNDEEP
sqlsql-server-2008t-sql
SkeetJon
SkeetJon

Reputation: 1491

Select Rows Where A Column Contains Any Result From A SubQuery

If have a table that contains a column of concatenated data using a delimiter i.e. 11111_22222_33333 (I know this is bad practice, but that's the data I have to work with)

I need to write a query that returns all rows that contain specific values of 22222 where the specific values are the result of a sub query.

So, what I am doing is follows...

SELECT * FROM myTable WHERE myColumn IN (SELECT [Name] FROM subTable)

This runs but doesnt return any results as it's specifically matching 1111_2222_3333 to 2222. So what I need is a way of using a LIKE or something similar

SELECT * FROM myTable WHERE myColumn LIKE (SELECT [NAME] FROM subTable)

gives the error 

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows 
=, !=, <, <=       , >, >= or when the subquery is used as an expression.

How would this be accomplished please? I am using Sql Server 2008 R2 to develop, but the solution needs to also be compatible with Server 2005

Upvotes: 1

Views: 4118

Answers (3)

Taryn
Taryn

Reputation: 247620

You can use a JOIN with the LIKE clause to get the result:

select *
from mytable t
inner join subtable s
   on t.mycolumn like '%'+s.name+'%'

See SQL Fiddle with Demo

Upvotes: 0

AdamL
AdamL

Reputation: 13141

SELECT 
    t1.* 
FROM myTable t1
join (
    SELECT distinct [Name] FROM subTable
) x
on t1.myColumn like '%' + x.[name] + '%'

Upvotes: 1

rs.
rs.

Reputation: 27417

Your subquery is returning more than one value and LIKE will not work with subquery, what you need to use is EXISTS here try this

SELECT * FROM myTable a 
WHERE EXISTS  (SELECT 1 FROM subTable b
               WHERE a.myColumn LIKE '%' + b.[NAME] + '%')

Upvotes: 2

Related Questions