what is wrong with this join ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

what is wrong with this join ?

Author

Topic

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2006-09-20 : 07:11:15

Hi
I am trying to join between 3 tables and group some data to get totals.
At the moment I have a query:
SELECT S.[Company Description], IIf(Len([Company Code])=2,"00" & [Company Code],[Company Code]) AS Expr1, ((Sum([1To90])+Sum([Not Due])+Sum([91To120]))/1000) AS Months0To3, (Sum(baddebt_CURR.BadDebt)) AS BADDebt
FROM ((SELECT [Company Code], [Company Description] FROM Structure GROUP BY [Company Code], [Company Description]) AS S
LEFT JOIN zarageddebt_CURR ON S.[Company Code] = zarageddebt_CURR.CoCd)
LEFT JOIN baddebt_CURR ON S.[Company Code] = baddebt_CURR.CoCd
GROUP BY S.[Company Description], IIf(Len([Company Code])=2,"00" & [Company Code],[Company Code])

The problem is the 3rd join onto the baddebt_CURR table. It is drastically multiplying the figures; I assume because of all the records in the 2nd table. What I want to do is join from S to zarageddebt_CURR and then from S to baddebt_CURR separately to group the figures.

ANy idea on how to do this ?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-20 : 07:51:45

Are you using SQL Server?

Chirag

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-09-20 : 08:10:48

Looks like it's correct. The LEFT JOINs will give you all (distinct) records from the Structure table.

If this isn't what you're looking for perhaps you could explain further or post some data/results.

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2006-09-20 : 09:03:18

Using Access, which is confusing me a bit due to all the brackets it puts round joins.

As an example...say I have the following data

Structure table:
CompanyCode
12

zarageddebt_CURR tabe
CompanyCode Total
12 100
12 100

baddebt_CURR table:
CompanyCode Total
12 10
12 10

When I join structure to zarageddebt_CURR and sum the total, grouped by the company code, I get a result of 200.
When I join Structure to zarageddebt_CURR and baddebt_CURR I get a result of 400 when summing and grouping.
I think this is because it joins on the company code twice from the zarageddebt_CURR table but I can't seem to bypass this effect.

Does make sense? Let me know if not and I can post up the DB or something...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-20 : 09:19:27

[code]SELECT s.[Company Description],
RIGHT('0000' + [Company Code], 4) AS Expr1,
(Sum([1To90]) + Sum([Not Due]) + Sum([91To120])) / 1000.0) AS Months0To3,
Sum(baddebt_CURR.BadDebt) AS BADDebt
FROM (
SELECT [Company Code],
[Company Description]
FROM Structure
GROUP BY [Company Code],
[Company Description]
) AS S
LEFT JOIN zarageddebt_CURR ON S.[Company Code] = zarageddebt_CURR.CoCd
LEFT JOIN baddebt_CURR ON S.[Company Code] = baddebt_CURR.CoCd
GROUP BY s.[Company Description],
RIGHT('0000' + [Company Code], 4)[/code]

Peter Larsson
Helsingborg, Sweden

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2006-09-20 : 09:25:51

Hi
That is the same problem because as soon as you join onto the baddebt_CURR table, it duplicates the figures due to the join. Try it with the 3 tables and the data above - you get 400 instead of 200...and 40 instead of 20 for the bad debt.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-20 : 09:36:13

[code]SELECT s.[Company Description],
RIGHT('0000' + [Company Code], 4) CompKey,
t.Months0To3,
y.BadDebt
FROM (
SELECT [Company Code],
[Company Description]
FROM Structure
GROUP BY [Company Code],
[Company Description]
) AS s
LEFT JOIN (
SELECT CoCd,
(Sum([1To90]) + Sum([Not Due]) + Sum([91To120])) / 1000.0) Months0To3
FROM zarageddebt_CURR
GROUP BY CoCd
) t ON t.CoCd = s.[Company Code]
LEFT JOIN (
SELECT CoCd,
SUM(baddebt_CURR.BadDebt) BadDebt
FROM baddebt_CURR
GROUP BY CoCd
) u ON u.CoCd = s.[Company Code]
[/code]

Peter Larsson
Helsingborg, Sweden

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2006-09-20 : 10:15:18

Neat little trick.
Thanks !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources