Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Using LIKE to find any of the fields in one table in the fields of another

    The title may be confusing, so here it is. I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).

    Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%

    But to the best of my knowledge, LIKE can only handle one comparison string.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about something like this?

    Code:
    SELECT pageName
     FROM  table_a AS a
     WHERE EXISTS
           (SELECT *
             FROM  tblitem AS b
             WHERE a.pageName LIKE '%' + b.itemNo + '%'
           )
    ;

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by tonkuma View Post
    How about something like this?

    Code:
    SELECT pageName
     FROM  table_a AS a
     WHERE EXISTS
           (SELECT *
             FROM  tblitem AS b
             WHERE a.pageName LIKE '%' + b.itemNo + '%'
           )
    ;
    excellent, thank you!

  4. #4
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    ok, so what if i also want to select the itemNo in addition to the page name?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What if adding itemNo like this way?
    Code:
    SELECT a.pageName
         , b.itemNo
     FROM  table_a AS a
     INNER JOIN
           tblitem AS b
      ON   a.pageName LIKE '%' + b.itemNo + '%'
    ;

  6. #6
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by tonkuma View Post
    What if adding itemNo like this way?
    Code:
    SELECT a.pageName
         , b.itemNo
     FROM  table_a AS a
     INNER JOIN
           tblitem AS b
      ON   a.pageName LIKE '%' + b.itemNo + '%'
    ;
    ah perfect. i was on the right track, just missed the syntax.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •