Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2008 > SQL Server 2008
|
SQL Server 2008 General discussion of SQL Server *2008* version only - not related to a specific book.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2008 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 8th, 2012, 02:41 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 112
Thanks: 12
Thanked 0 Times in 0 Posts
Default Issues joining and union tables ???

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
 
Old November 11th, 2012, 09:04 PM
Registered User
 
Join Date: Oct 2012
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Last edited by bcbarnes; November 12th, 2012 at 06:15 AM..





Similar Threads
Thread Thread Starter Forum Replies Last Post
joining three tables nasirmunir MySQL 3 March 18th, 2009 02:31 PM
joining tables pit_bait SQL Language 2 September 28th, 2007 07:29 AM
Joining Three Tables Alexpizzoferro Classic ASP Databases 5 September 1st, 2006 04:08 PM
joining 3 tables koskalgr MySQL 5 March 24th, 2006 09:52 AM
Joining two tables rajustha SQL Server 2000 1 December 7th, 2003 07:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.