#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2015
    Posts
    1
    Rep Power
    0

    Why does this query NOT produce results?


    I wrote and executed this query:

    Code:
    select poitem.partnum, poitem.description, poitem.qtyfulfilled
    
    from poitem
    inner join part on poitem.partid = part.id
    inner join product on part.id = product.partid
    
    where poitem.datelastfulfillment >= '1-1-2015'
    and product.id = 65
    and product.id not in (select productid from soitem
        where soitem.datelastfulfillment >= '1-1-2015'
        and soitem.productid = 65)
    It returned one record: 54527, CALCIUM FILLING GUN 54527, 1

    All good.

    However, when I remove the single product specification:

    select poitem.partnum, poitem.description, poitem.qtyfulfilled
    Code:
    from poitem
    inner join part on poitem.partid = part.id
    inner join product on part.id = product.partid
    
    where poitem.datelastfulfillment >= '1-1-2015'
    and product.id not in (select productid from soitem
        where soitem.datelastfulfillment >= '1-1-2015')
    NO RECORDS are returned!

    I checked and verified, there should be many records.
    The point of the query is to determine what parts/ products
    were purchased this year, but not one single unit was sold.

    This is a Fishbowl/ Firebird query.
    Can anyone tell me why this is happening?

    Thanks for looking!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    233
    Rep Power
    15
    Code:
    select productid from soitem
        where soitem.datelastfulfillment >= '1-1-2015'
    - run only this subquery and see what are the results from it. Because you search for items that are not in this list. If the subquery returns many, or all items it's normal not to have any results. Because of the not in statement in the main query only items not in the list of the subquery will be fetched.

IMN logo majestic logo threadwatch logo seochat tools logo