Complex Join statement (4 tables)

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

Transact-SQL (2000)

Complex Join statement (4 tables)

Author

Topic

aJaggedsky
Starting Member

1 Post

Posted - 2006-11-01 : 19:44:38

Select sum(SOP30200.SUBTOTAL),Count(SOP30200.SOPNUMBE)
From SOP30200, SOP10106
Where SOP30200.SOPTYPE = '3'
AND SOP30200.SOPNUMBE = SOP10106.SOPNUMBE
AND SOP30200.DOCDATE BETWEEN @STARTMONTH AND @CURRENTMONTHEND
AND SOP10106.USRTAB01 <> ''
GROUP BY SOP10106.USRTAB01
Returns the correct results of:
70.17000 1
7637.50000 3

Incorporating this logic into this join statement returns incorrect data in the last column, should be 1 and 3.

Select UPR00100.EMPLOYID,sum(PA30101.PABILLED_AMOUNT) Billings,SOP10106.USRTAB01 , Count(SOP30200.SOPNUMBE)--sum(SOP30200.SUBTOTAL) As Additional
From PA30101
Inner Join UPR00100 ON PA30101.EMPLOYID = UPR00100.EMPLOYID
AND PA30101.PADT BETWEEN @STARTMONTH AND @CURRENTMONTHEND
Left Outer Join SOP10106 ON PA30101.EMPLOYID = SOP10106.USRTAB01
AND SOP10106.USRTAB01 <> ''
Left Outer Join SOP30200 ON SOP10106.SOPNUMBE = SOP30200.SOPNUMBE
And SOP30200.SOPTYPE = '3'
AND SOP30200.DOCDATE BETWEEN @STARTMONTH AND @CURRENTMONTHEND
Group By UPR00100.EMPLOYID,SOP10106.USRTAB01
ORDER BY UPR00100.EMPLOYID

a 114064.58000 a 955
b 37275.00000 NULL 0
c 36662.42000 NULL 0
d 22235.00000 NULL 0
e 94962.51000 NULL 0
f 204663.75000 f 771
g 45507.25000 NULL 0

Please help me find my flaw in the logic! Thanks!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-02 : 01:30:17

For the first, you are INNER JOINing more columns, thus creating more combinations of total selected rows.
Second, try to rewrite COUNT() as COUNT(DISTINCT...).

It is hard to tell what is wrong as long as we don't have access to source/sample data.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources