Results 1 to 2 of 2

Thread: SQL Query

  1. #1
    Join Date
    Oct 2003
    Posts
    54

    Unanswered: SQL Query

    I've been working with a SQL query for the last day or so that I can't seem to get working. I have 3 tables with the following structure:

    Table - GM_INV_LOC

    SKU_NUM AVAIL_QTY LOC_CD
    ------------ --------- -------
    000018716-04 9 0023
    000018716-04 12 0124

    Table - GM_SKU

    SKU_NUM ITM_CD
    ------------ ------------
    000019986-00 000019986
    000019986-01 000019986


    Table - GM_SKU2STORE

    SKU_NUM MIN_QTY MAX_QTY
    ------------ ------- -------
    000842172-04 1 1
    000842172-04 1 1

    I'm using the following query:

    "select distinct GM_SKU.ITM_CD
    from GM_SKU, GM_SKU2STORE, GM_INV_LOC
    where GM_INV_LOC.STORE_CD='0001'
    and GM_SKU.SKU_NUM = GM_SKU2STORE.SKU_NUM
    and GM_SKU.SKU_NUM = GM_INV_LOC.SKU_NUM
    group by GM_SKU.ITM_CD
    having sum(GM_SKU2STORE.MIN_QTY) < 1
    and sum(GM_INV_LOC.AVAIL_QTY) > 0"

    I realize that this is ugly SQL but for some reason, this thing has got me lost. All I want to do is query for the "itm_cd" in the "gm_sku" table and return the item codes for everything that has a "sum(min_qty)<1" from the "gm_sku2store" table and an "avail_qty>0" from the "gm_inv_loc" table for the "store_cd='0001'". All 3 tables have multiple entries of the same SKU, but each SKU only has 1 "itm_cd". I'd like the results to show only one record for each item code but I keep getting:

    ITM_CD SKU_NUM
    ------------ ------------
    000015124 000015124-24
    000023952 000023952-02
    000023952 000023952-03
    000023952 000023952-04
    000023952 000023952-05
    000023952 000023952-06
    000023952 000023952-07
    000023952 000023952-08
    000023952 000023952-09
    000839804 000839804-01
    000839804 000839804-02
    000839804 000839804-03
    000839804 000839804-04
    000859517 000859517-01
    000859517 000859517-02
    000859517 000859517-03
    000859517 000859517-04
    923346832 923346832-11

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    SELECT DISTINCT evaluates the entire record as one entity. Therefore, if the SKU is different, it is viewed as a "distinct" record and will be printed. Try leaving off the DISTINCT. Since you are including GM_SKU.ITM_CD in the group by clause, you should only return one ITM_CD and the summation should execute properly.

Posting Permissions

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