Query to get rows if value contains result of another query

Question

Post reply

Query to get rows if value contains result of another query

IT researcher

SSCertifiable

Points: 7569
More actions
March 5, 2013 at 2:46 am

#290652

hi,
I have searching for a query that returns a row if column value contains result of another query. Its like "select * from table1 where col contains(select col1 from table2 where <condition>)". Its just like using sub query in IN,but i want contains instead. Is there any way i can do this??
Thank you

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

winmansoft (3/5/2013)
Its just like using sub query in IN,but i want contains instead.

I aqm not sure what you are asking ? please clarify

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

John.Sansom SSCertifiable Points: 7335 More actions · Answer 2

Have a look at the query examples for the T-SQL operator EXISTS()

John Sansom (@sqlBrit) | www.johnsansom.com

IT researcher SSCertifiable Points: 7569 More actions · Answer 3

Its just like "SELECT * FROM entries WHERE work IN (select work from entries1 WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013')". But IN matches with exact values from result column of sub query. But i want to change matching to partial.

Gazareth One Orange Chip Points: 27737 More actions · Answer 4

EXISTS might work. CONTAINS is used for querying full-text indexes.

But I think we need to understand what you need first. What do you mean by changing matched to partial?

Thanks

IT researcher SSCertifiable Points: 7569 More actions · Answer 5

I will explain with example. I have a query "select work from entries1 WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'" and lets say it will return result

training

checkout

surfing

develop

...

I have another table and which has Work column and it contains value 'checkout and play' in some row.I have to develop a query like "SELECT * FROM entries WHERE work IN (select work from entries1)" and i want row which contains 'checkout and play' in result. IN does not do it because it will not match 'checkout' with 'checkout and play' although 'checkout and play' contains 'checkout'.I want some other way to do it

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

winmansoft (3/5/2013)
I will explain with example. I have a query "select work from entries1 WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'" and lets say it will return result
training
checkout
surfing
develop
...
I have another table and which has Work column and it contains value 'checkout and play' in some row.I have to develop a query like "SELECT * FROM entries WHERE work IN (select work from entries1)" and i want row which contains 'checkout and play' in result. IN does not do it because it will not match 'checkout' with 'checkout and play' although 'checkout and play' contains 'checkout'.I want some other way to do it

INNER JOIN the two tables using LIKE. If that doesn't work, you will need to split out the words in 'checkout and play'. It will work but it won't be quick.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

winmansoft (3/5/2013)
I have searching for a query that returns a row if column value contains result of another query. Its like "select * from table1 where col contains(select col1 from table2 where <condition>)". Its just like using sub query in IN,but i want contains instead. Is there any way i can do this??

Coming back to your first post , if you are using CONTAINS(fulltext search) then you can use FREETEXT there

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

IT researcher SSCertifiable Points: 7569 More actions · Answer 8

Bhuvnesh (3/5/2013)
winmansoft (3/5/2013)
I have searching for a query that returns a row if column value contains result of another query. Its like "select * from table1 where col contains(select col1 from table2 where <condition>)". Its just like using sub query in IN,but i want contains instead. Is there any way i can do this??
Coming back to your first post , if you are using CONTAINS(fulltext search) then you can use FREETEXT there

"select * from table1 where col contains(select col1 from table2 where <condition>)" wont work

IT researcher SSCertifiable Points: 7569 More actions · Answer 9

ChrisM@Work (3/5/2013)
INNER JOIN the two tables using LIKE. If that doesn't work, you will need to split out the words in 'checkout and play'. It will work but it won't be quick.

Ca you write inner join query? and what do you mean by splitting the words?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 10

winmansoft (3/5/2013)
Bhuvnesh (3/5/2013)
winmansoft (3/5/2013)
I have searching for a query that returns a row if column value contains result of another query. Its like "select * from table1 where col contains(select col1 from table2 where <condition>)". Its just like using sub query in IN,but i want contains instead. Is there any way i can do this??
Coming back to your first post , if you are using CONTAINS(fulltext search) then you can use FREETEXT there
"select * from table1 where col contains(select col1 from table2 where <condition>)" wont work

See this link http://msdn.microsoft.com/en-us/library/ms176078(v=sql.105).aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

winmansoft (3/5/2013)
ChrisM@Work (3/5/2013)
INNER JOIN the two tables using LIKE. If that doesn't work, you will need to split out the words in 'checkout and play'. It will work but it won't be quick.
Ca you write inner join query? and what do you mean by splitting the words?

This is the simplest way to write it. If it generates dupes, then we'll change one side to a derived table. Can you use something a little more realistic than "entries" and "entries1", or is that what they are really called?

SELECT *

FROM entries e

INNER JOIN entries1 e1

ON e1.work LIKE e.work+'%'

AND e1.[date] BETWEEN '01-Jan-2012' and '20-Jan-2013'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

IT researcher SSCertifiable Points: 7569 More actions · Answer 12

Bhuvnesh (3/5/2013)
See this link http://msdn.microsoft.com/en-us/library/ms176078(v=sql.105).aspx

Can use query instead of @SearchWord?? i dont think so

Gazareth One Orange Chip Points: 27737 More actions · Answer 13

You need to implement Full-Text Indexing before freetext or contains will work.

I think Chris's solution is the way to go unless you find it's not what you need or that the performance isn't good enough, which could be quite likely with joins using like.

Depends on your data, but you might need to do INNER JOIN entries1 e1 ON e1.work LIKE '%'+e.work+'%'

But that will make performance even worse!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Gazareth (3/5/2013)
You need to implement Full-Text Indexing before freetext or contains will work.
I think Chris's solution is the way to go unless you find it's not what you need or that the performance isn't good enough, which could be quite likely with joins using like.
Depends on your data, but you might need to do INNER JOIN entries1 e1 ON e1.work LIKE '%'+e.work+'%'
But that will make performance even worse!

+1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden