I've been working with a SQL query for the last day or so that I can't seem to get it working correctly. 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


...perhaps removing the "SKU_NUM" reference from the select statement?

hi,

i am a bit confused here. The select u show, has only one column in the select clause, but the results have to columns ??????
If you only want the ITM_CD to be shown and have it distinct (so only one value is shown) yes, you'll have to drop the second column (SKU_NUM). Since each ITM_CD can have multiple SKU_NUM's, showing them both will give u the result set shown. Select DISTINCT means that oracle filter out all rows from the result set that are really the same. Since u display the SKU_NUM column also and ,ultiple SKU_NUMs are referring to the same ITM_CD, multiple rows of are shown per ITM_CD.

Just take a look at your resultset and see that all the rows are different because of the SKU_NUM.

Hope this helps.