2

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.

Improve this question
4
...
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

Improve this answer
1
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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