Avoid results of subquery as sum of items

Database
Fan

Avoid results of subquery as sum of items

SELECT STID.SALES_TRANS_DATE
    ,STID.ITEM_ID
    ,STID.ITEM_QTY
        
FROM  SALES_TRANS_ITEM_DLY AS STID  
    
WHERE STID.SALES_TRANS_DATE between '2011-01-01' and '2012-09-01'
AND  STID.ITEM_ID IN( SELECT  ID.ITEM_ID
                      FROM  ITEM_DIM AS ID
            WHERE  ID.DEPT_ID IN(4233)
               AND   ID.FAMILY_GROUP_ID IN(124)
             )
  
ORDER BY STID.SALES_TRANS_DT    

Hello all.

I am testing how to write subqueries in TD, but it is not returning the results I expected.

The above SQL returns the SUM of ITEM_QTY per day. I know multiple transactions happen during any day and expected that result.

Why am I not getting each transaction then?

Thanks.

1 REPLY
Enthusiast

Re: Avoid results of subquery as sum of items

One possible reason for not getting the results could be that the sales transactions in SALES_TRANS_ITEM_DLY, but their ITEM_ID doesn't exist in ITEM_DIM.

Your SQL query looks fine, the only problem seems to be the missing data in ITEM_DIM table.

The below mentioned query looks to be the equivalent sql query without subquery, and if you are getting some rows for this query then the subquery needs a review!

SELECT STID.SALES_TRANS_DATE
,STID.ITEM_ID
,STID.ITEM_QTY
FROM SALES_TRANS_ITEM_DLY AS STID
INNER JOIN ITEM_DIM B
ON A.ITEM_ID = B.ITEM_ID
WHERE STID.SALES_TRANS_DATE between '2011-01-01' and '2012-09-01'
AND B.DEPT_ID IN (4223)
AND B.FAMILY_GROUP_ID IN (124)
ORDER BY STID.SALES_TRANS_DT