Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2014
    Posts
    1

    Unanswered: Not Like on Field with WildCards

    Hello,

    I have a table (Begins) with 1 field that contains entries of word prefixes. I would like to look in the column (ID) of another table (Scan Data) for any entries that include that prefix and exclude them from the results.

    I wrote

    Code:
    SELECT  [Scan Data].[ID]
    FROM [Scan Data], Begins
    WHERE [Scan Data].[ID] NOT LIKE ([Begins].[ID]+'*');
    This does not seem to work properly. When I remove "not" then it returns only results that start with the entries in the prefix table, however when I add "not" some of the prefixes are still included.

    Here is a sample of what I want to do
    Code:
    Scan Data
    |ID       |
    |Chevy    |
    |Ford     |
    |GMC     |
    |Fiat    |
    
    Begins
    |ID    |
    |F     |
    |Ch    |
    
    Results
    |GMC |
    Thanks for your help.

    -Andrew
    Last edited by andrewc90; 06-27-14 at 02:26.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So you probably need to do left join and filter out values where the right side of the join is not null. The mismatched query wizard should take you most of the way on this. Except you will need to limit the numbers matched using the left function
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a way of doing what you want is:-
    Code:
    SELECT ScanData.Code
    FROM  ScanData
    left join begins on left(scandata.code,len(Begins.prefix)) =  Begins.prefix
    where isnull(begins.prefix )
    order by scandata.code
    change the column names as required
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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