Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: using IN and LIKE in same expression

    Hi

    I have a list of the first part of a postcode in a databse, eg DE, LS etc

    Code:
    SELECT column1 FROM table1
    WHERE (postcode IN(SELECT postcode from table2 where var1 = 'val1'))
    how ever this only selects exact matches from table2 and i need to match it against complete postcodes, eg DE12 3AA, i need to match postcode in table1 then use the wildcard. Might be easier if i show you my [non] logical way of thinking (i know this is wrog!):

    Code:
    ...
    WHERE (postcode IN( LIKE (SELECT postcode from table2 where var1 = 'val1') + '%'))
    cheers

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i never use uncorreloated subqueries but does....
    Code:
    SELECT column1 FROM table1
    WHERE (postcode like (SELECT postcode + '%' from table2 where var1 = 'val1'))
    ...work?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Anything wrong with this?
    Code:
    SELECT *
    FROM   addresses
     INNER
      JOIN postcodes
        ON Left(addresses.postcode, 2) = postcodes.first_two_letters
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    not sure. would LEFT scrub any index utilization?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    Quote Originally Posted by Thrasymachus
    i never use uncorreloated subqueries but does....
    Code:
    SELECT column1 FROM table1
    WHERE (postcode like (SELECT postcode + '%' from table2 where var1 = 'val1'))
    ...work?
    Doesn't work becuase:

    Code:
    SELECT postcode + '%' from table2 where var1 = 'val1
    ....returns more than one result as there are more than one postcodes retrieved. Thats why i was trying to use 'IN' somewhere..

    cheers

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    please see the directions regarding posting questions at the top of this forum.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    does this help.....
    SELECT column1 FROM table1
    WHERE (postcode IN(SELECT postcode from table2 where var1 LIKE 'val1%'))

Posting Permissions

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