Joining a select statment to a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Joining a select statment to a table

Author

Topic

DrEvilBeer
Starting Member

4 Posts

Posted - 2002-07-24 : 17:40:03

Here's what I'm trying to do...
I have one table that has a number in it and I want to verify that number against the sum of some rows from two other tables.
The only way I know how to do this is to join the two other tables two the first table and then do a calculation like tableA.Number <> (SUM(tableB.Number) + SUM(tableC.Number))

The problem is that when I do this I get an incorrect calculation on my SUM statements because the joined tables are causing each other to duplicate.

I probably didn't explain this very well so I included my code below and the resultset it gives.

SELECT IV00102.LOCNCODE, IV00102.ITEMNMBR,
IV00102.ATYALLOC, SUM(TRXQTY) AS CalcInvAlloc,
SUM(SOP10200.ATYALLOC) AS CalcSOPAlloc
FROM IV00102 LEFT JOIN
IV10001 ON IV00102.LOCNCODE = IV10001.TRXLOCTN AND
IV00102.ITEMNMBR = IV10001.ITEMNMBR AND
((IVDOCTYP = 3 AND TRFQTYTY = 1) OR
(IVDOCTYP = 1 AND TRXQTY < 0)) LEFT JOIN
SOP10200 ON
IV00102.LOCNCODE = SOP10200.LOCNCODE AND
IV00102.ITEMNMBR = SOP10200.ITEMNMBR AND
(SOPTYPE = 2 OR
SOPTYPE = 3)
WHERE RCRDTYPE = 2 AND
IV00102.ITEMNMBR = '10516'
GROUP BY IV00102.LOCNCODE, IV00102.ITEMNMBR,
IV00102.ATYALLOC
HAVING IV00102.ATYALLOC <> SUM(TRXQTY)
+ SUM(SOP10200.ATYALLOC)
ORDER BY IV00102.ITEMNMBR ASC, IV00102.LOCNCODE ASC

(The line "IV00102.ITEMNMBR = '10516'" will be taken out when I put this statement into production, it's simply there so I can look at a single example.)

Result:

LOCNCODE ITEMNMBR ATYALLOC CalcInvAlloc CalcSOPAlloc
--------- --------- --------- ------------- -------------
022 10516 3.00000 10.00000 2.00000

The "CalcInvAlloc" column should actually be "1" but because the same record is showing up 10 times to match the 10 records i nthe CalcSOPAlloc column, I'm getting a calculation of "10". So what I want to do is summarize the two tables being joined before I join them. I was wondering if there was a way to join a select statement where I can group the records into a single record first before joining them to the IV00102 table so only a single record is being joined from both IV10001 and SOP00102. Does this make sense? Thanks!

-Brandon

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-24 : 19:17:24

tableA.Number <> (SUM(tableB.Number) + SUM(tableC.Number))

Sorry, I'm low on time here, so I didn't quite get a chance to fully read through... But try subquerying this.
tableA.number <> ((select sum(number) from tableb) + (selet sum(number) from tablec))

-----------------------
Take my advice, I dare ya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources