Hi to all,
need some help on my select statement...

trying to join the following 2 select statement but not coming out the way i need it...

1st select returns all info needed...
2nd select get me all units sold between the dates...
together i want to return all info having the units sold for each store else put a zero

select * from (
SELECT StoreID as 'StoreID',City as 'City', S.INUMBR as 'SKU',IBPOOQ as 'IBPOOQ',
sum(S.IBHAND+S.IBINTQ) as 'OH',I.ATT as 'ATT',sum(IBWKCR) as 'TW',sum(IBWK01) as 'LW',
SUM(IBRSUP) as 'MTD',SUM(IUSECR) as 'STD', SUM(IUSE02) as 'SSLY',
SUM(IUSE01) as 'PS', SUM(IUSE03) as 'PSLY'
FROM MMS_SKUStore S,Stores,MMS_SKUXRef X, MMS_INVBAL_WHS I
WHERE S.INUMBR=X.INUMBR AND StoreID=S.ISTORE
AND MSTSKU='101-00001'
AND S.INUMBR=I.INUMBR AND I.ISTORE = 4
GROUP BY StoreID,City,S.INUMBR,IBPOOQ,I.ATT
) as EAM
join
(
select SUM(H.SKSNU) as 'Units',MMS_SKUXRef.INUMBR, H.ITRLOC from
HARSLS H
join MMS_SKUXRef on MMS_SKUXRef.INUMBR = H.INUMBR
where H.ITRDAT between '121001' and '121007'
group by MMS_SKUXRef.INUMBR, H.ITRLOC
) as LDF
on EAM.SKU = LDF.INUMBR and EAM.StoreID = LDF.ITRLOC


thank you in advance
Rino

Are you getting an error, or just not the expected data?

You can try running the two queries individually to see if they give you the expected results. A lot of times, I find that my joins aren't working as expected. Especially with aggregates. Order by the "ON" criteria, and match up the results against each other. Often, you will find that a join table is returning multiple right hand rows for the single left hand row, causing aggregates to be overstated.

Also, two notes just about style. I don't have this book so I don't know what the author says, but

1. I always fully spell out the type of join. Instead of just 'join' I would put 'inner join' , etc.
2. I never specify anything other than a single table in the 'from' list. To me, this is confusing, and I'd much rather use a join of some type.

Brian