Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013
    Posts
    4

    Unanswered: Using WildCards in Conjunction with the IN keyword?

    This is the Serial number I am Searching my Database for: "ALCLL5096747"

    My Query:

    SELECT
    tbl_shop.shop_desc,
    tbl_location.location_desc,
    tbl_inventory.location_id,
    tbl_inventory.shop_id,
    tbl_inventory.uid,
    tbl_inventory.part_number,
    tbl_inventory.model_number,
    tbl_inventory.serial_number,
    tbl_inventory.desc,
    tbl_inventory.date_modified
    FROM tbl_inventory
    INNER JOIN tbl_shop ON tbl_inventory.shop_id = tbl_shop.shop_id
    INNER JOIN tbl_location ON tbl_inventory.location_id = tbl_location.location_id WHERE '5096747' IN (tbl_inventory.model_number, tbl_inventory.part_number, tbl_inventory.serial_number)
    ORDER BY CASE tbl_inventory.location_id WHEN 1 THEN 2 ELSE 1 END , tbl_inventory.location_id

    I'm having troubles pulling the serial number as Written in the query above (WHERE '5096747').

    I'm pulling the information off a BarCode which supplies the last portion (Just the numbers) of the serial number.

    When I search 5096747 this query returns nothing however, if I were to search 'L5096747' my part is reflected in our database. is anyone able to explain to me why this is?

    I have tried using wildcards in front and behind the serial number to no avail.
    "%5096747", "%5096747%", "5096747%"

    I need my query to return anything that contains '5096747' in those three columns.

    I tried 'LIKE'.. but in conjunction with 'IN' i can't make any headway.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    The clause WHERE expression IN (expr1,expr2) cannot accept wildcards and performs equality operations with the expressions inside the parenthesis. This is identical to writing the query WHERE expression = expr1 OR expression = expr2. You would need to rewrite your where clause as follows:

    WHERE '5096747' IN (tbl_inventory.model_number, tbl_inventory.part_number, tbl_inventory.serial_number)

    to

    WHERE tbl_inventory.model_number LIKE '%5096747%'
    OR tbl_inventory.part_number LIKE '%5096747%'
    OR tbl_inventory.serial_number LIKE '%5096747%'
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Mar 2013
    Posts
    4
    Quote Originally Posted by it-iss.com View Post
    The clause WHERE expression IN (expr1,expr2) cannot accept wildcards and performs equality operations with the expressions inside the parenthesis. This is identical to writing the query WHERE expression = expr1 OR expression = expr2. You would need to rewrite your where clause as follows:

    WHERE '5096747' IN (tbl_inventory.model_number, tbl_inventory.part_number, tbl_inventory.serial_number)

    to

    WHERE tbl_inventory.model_number LIKE '%5096747%'
    OR tbl_inventory.part_number LIKE '%5096747%'
    OR tbl_inventory.serial_number LIKE '%5096747%'
    Excellent, that works flawlessly. I appreciate your input. I had thought we had tried that originally and it didn't work as it was returning 200+ records when only 3 matched.. after reviewing your comment and re-writing the query it works flawlessly.. not sure what I left out the first time!

    Thanks, again!

Posting Permissions

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