Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need some help writing a select statement (I still don't think in sets!). Here's the scenario:

I have table A with column "Name varchar(20)". I have table B with column "SearchTerm varchar(20)". I want to query A for any rows that are like any search term in B.

Let me give specific data:

Table A has:

Jay
Jim
Frank
George

If table B has 'a', then the results would be:

Jay
Frank

If table B has 'j', then the results would be:

Jay
Jim

If table B has 'y' and 'g' (two rows), then the results would be:

Jay
George

If table B has 'Jay', then the results would be:

Jay

I actually want to be able to apply this idea to be able to search two columns, FirstName and LastName, but I'm guessing that once I see the idea, I'll be able to generalize the solution to search multiple columns.

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted
...
FROM
  TableA
  JOIN
  TableB ON
          TableA.FirstName LIKE '%' + TableB.SearchTerm + '%' OR 
          TableA.LastName LIKE '%' + TableB.SearchTerm + '%'
....

Now, this isn't going to be the best of queries. Consider full text search instead (I'm not too familiar with it)

This OR could be split into a UNION

share|improve this answer
    
Sweet! Thanks! One thing I read is that full text search (which was what I was considering using initially) is intended more for large blocks of text rather than narrow columns. Regardless, your solution works great and will be fine for now. Will do more research as to whether or not I should use full text. I was trying to figure out how to do this with a subquery. Your solution is nice and simple (now that I've seen it!). –  birdus Jul 8 '11 at 21:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.