Consulting

Results 1 to 8 of 8

Thread: SQL Syntax Question - LIKE Operator

  1. #1
    Administrator
    2nd VP-Knowledge Base
    VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location

    SQL Syntax Question - LIKE Operator

    All,

    One of my first projects was to collect a sum of quantity of a particular type of item that we have in our database. The problem with this was the item's description was the ONLY way I can SURELY obtain the correct data.

    For example, we sell safety harnesses at my job. Each safety harness has multiple components, and even more sub-components (and sub-components of sub-components and so on...). To collect ONLY the restraints that we sold, I had to resort to using the Description column of the Items table using the LIKE operator in my SQL query. Here is an example:
    SELECT SUM(TItems.Item)
    FROM TItems
    WHERE TItems.Description LIKE '%restraint%'
    And that could have been okay...but they wanted more specific types of restraints (like for Aviation, Ambulances, etc)...and once again I had to use a LIKE operator in the SQL query like:
    SELECT SUM(TItems.Item)
    FROM TItems
    WHERE TItems.Description LIKE '%restraint%' OR TItems.Description LIKE '%kit%'
    But the more criteria I was getting, the bigger the query kept getting (and it wasn't dynamic). There is NO other way to collect this info the way they are asking for it, and I've mentioned it to them.

    So I thought I could come up with a table similar to:
    tCriteria (name of table)
    MyCriteria
    *restraint*
    *kit*
    And use a syntax like this:
     SELECT SUM(TItems.Item)
     FROM TItems, tCriteria
     WHERE TItems.Description LIKE tCriteria.MyCriteria
    Which did work...but how does it work? I'm just a little confused on this...because if, for example, you have in the table:
    tCriteria (name of table)
    MyCriteria
    *restraint*
    *restraint*
    It will double the sum for Descriptions that contain the word "restraint" in it...so does that mean my original table:

    tCriteria (name of table)
    MyCriteria
    *restraint*
    *kit*
    Would double the sum of something like:

    Description:
    5-pt,restraint,kit,blah,blah,blah




    Also, how would anybody else approach this?




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  2. #2
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by malik641
    Also, how would anybody else approach this?
    I would use IN with a Subselect. .02

  3. #3
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    How do you use IN with the LIKE operator?




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  4. #4
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by malik641
    How do you use IN with the LIKE operator?
    SELECT [field] from T1 WHERE [Field] IN (SELECT [Field] FROM T2 WHERE T2.[Field1] LIKE [something] OR LIKE [somethingelse] OR....)

    or use WHERE EXIST

    or even better, write a 'contains' UDF [if using Access] and call it directly.

  5. #5
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Quote Originally Posted by stanl
    SELECT [field] from T1 WHERE [Field] IN (SELECT [Field] FROM T2 WHERE T2.[Field1] LIKE [something] OR LIKE [somethingelse] OR....)
    But the problem with that is what I was mentioned before about writing a bunch of "OR LIKE"'s.

    The way the WHERE clause in the following query:
     SELECT SUM(TItems.Item)
     FROM TItems, tCriteria
     WHERE TItems.Description LIKE tCriteria.MyCriteria;
    Works now is:

    WHERE TItems.Description
    LIKE [Row 1 of tCriteria.MyCriteria]
    OR LIKE [Row 2 of tCriteria.MyCriteria]
    OR LIKE [Row ... of tCriteria.MyCriteria]
    OR LIKE [Row n of tCriteria.MyCriteria]


    Quote Originally Posted by Stanl
    or even better, write a 'contains' UDF [if using Access] and call it directly.
    I will have to look into this. I'm not so familiar with it.




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  6. #6
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    as I first said, I think you need a subselect. If indeed your criteria are in a table then perhaps a SELECT WHERE LIKE... SELECT ( IN() ) or other variations could be used. This will be an Access Select, or SQL Server [where Transact-SQL offers more features]?

    Stan

  7. #7
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    I'll give it a shot.




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  8. #8
    VBAX Master XLGibbs's Avatar
    Joined
    Jan 2006
    Location
    state of confusion, but vacation in denial
    Posts
    1,315
    Location
    Malik,

    If you have multiple criteria, it makes sense to use the second table and either do:
    [vba]
    Select Sum(Table.Items)
    From Table
    Where TI.[CriteriaField] in (Select CriteriaColumn FRom CriteraTable)
    [/vba]

    You can also link using a join
    [VBA]
    Select Sum(Table.Items)
    From Table inner join CriteriaTable on Table.FieldName = CriteriaTable.Criterea
    [/vba]

    Which would only return the sum of matching rows where the criteria field in Table matches the values in the Criteria table.

    You can also use dynamic SQL, but with that many variables and LIKE syntax..it gets "messy"
    If you have posted the same question at multiple forums, please read this IMPORTANT INFO.

    Please use the thread tools to mark your thread Solved


    Please review the Knowledge Base
    for samples and solutions , or to submit your own!




Posting Permissions

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