Select question - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Select question

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-04-28 : 10:15:28

I have several tables

debt
----

debtID amount
------ ------
123 1000
202 2500
134 4500

debtors
-------

debtID debtorNo AmountPaid
------ -------- ----------
123 1 50
123 2 30
202 1 50

I want to return

debtid amount debtorNo amountPaid balance

123 1000 1 50 920
123 1000 2 30 920
202 2500 1 50 2450

How can I do this please?

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-28 : 10:24:38

SELECT a.debtid, a.amount, b.debtorNo, b.amountPaid, (a.amount-c.totalAmountPaid) AS balance FROM [debt] a INNER JOIN [debtors] b ON a.debtid = b.debtid INNER JOIN (
SELECT debtid,SUM(amountPaid) AS totalAmountPaid FROM [debtors] GROUP BY debtid ) AS c ON a.debtid = c.debtid

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-04-28 : 10:42:16

quote:
Originally posted by whitefang

SELECT a.debtid, a.amount, b.debtorNo, b.amountPaid, (a.amount-c.totalAmountPaid) AS balance FROM [debt] a INNER JOIN [debtors] b ON a.debtid = b.debtid INNER JOIN (
SELECT debtid,SUM(amountPaid) AS totalAmountPaid FROM [debtors] GROUP BY debtid ) AS c ON a.debtid = c.debtid

Your query yields the following, which is incorrect:

123 1000 1 50 950
123 1000 2 30 970
202 2500 1 50 2450

This will give what the OP requested:

SELECT a.debtid, a.amount, b.debtNo, b.amountPaid,
a.amount - (select sum(c.amountPaid) from [debtors] c where a.debtid = c.debtid group by c.debtid) AS balance
FROM [debt] a INNER JOIN [debtors] b ON a.debtid = b.debtid

--Edit: You modified your post, which is now correct.

Terry

-- Procrastinate now!

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-28 : 10:48:01

Inner join is faster than the subquery.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 10:52:04

Not always. And the subquery and INNER JOIN are different in terms of functionality too.
Compare the two execution plan and compare the numbers in sql profiler.

E 12°55'05.63"
N 56°04'39.26"

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-28 : 10:54:39

I meant that it was likely faster in this case (also depending on how he has his DB structure setup).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 11:14:26

[code]DECLARE @Debt TABLE
(
debtID INT,
amount INT
)

INSERT @Debt
SELECT 123, 1000 UNION ALL
SELECT 202, 2500 UNION ALL
SELECT 134, 4500

DECLARE @Debtors TABLE
(
debtID INT,
debtorNo INT,
AmountPaid INT
)

INSERT @Debtors
SELECT 123, 1, 50 UNION ALL
SELECT 123, 2, 30 UNION ALL
SELECT 202, 1, 50

-- Peso (25% of batch, 37 reads)
SELECT a.debtID,
a.amount,
b.debtorNo,
b.amountPaid,
a.amount - SUM(b.amountPaid) OVER (PARTITION BY a.debtID) AS balance
FROM @Debt AS a
INNER JOIN @Debtors AS b ON b.debtID = a.debtID

-- WhiteFang (41% of batch, 9 reads)
SELECT a.debtid,
a.amount,
b.debtorNo,
b.amountPaid,
a.amount - c.totalAmountPaid AS balance
FROM @debt AS a
INNER JOIN @debtors AS b ON a.debtid = b.debtid
INNER JOIN (
SELECT debtid,
SUM(amountPaid) AS totalAmountPaid
FROM @debtors
GROUP BY debtid
) AS c ON a.debtid = c.debtid

-- tosscrosby (20% of batch, 15 reads)
SELECT a.debtid,
a.amount,
b.debtorNo,
b.amountPaid,
a.amount - (select sum(c.amountPaid) from @debtors AS c where a.debtid = c.debtid group by c.debtid) AS balance
FROM @debt AS a
INNER JOIN @debtors AS b ON a.debtid = b.debtid[/code]

Statistics are[code]-- Peso
Table 'Worktable'. Scan count 4, logical reads 26.
Table '@Debt'. Scan count 1, logical reads 1.
Table '@Debtors'. Scan count 1, logical reads 1.

-- WhiteFang
Table 'Worktable'. Scan count 0, logical reads 0.
Table '@Debtors'. Scan count 2, logical reads 2.
Table '@Debt'. Scan count 1, logical reads 1.

-- tosscrossby
Table '@Debtors'. Scan count 4, logical reads 4.
Table 'Worktable'. Scan count 0, logical reads 0.
Table '@Debt'. Scan count 1, logical reads 1.[/code]

There is a bug in SET STATISTICS IO ON for INNER HASH JOIN. See connect.microsoft.com

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 11:23:57

For two tables with a few thousand records each, WhiteFang and Peso runs within 5% of time, but WhiteFang has lower read count.
I must say it was not easy to find a query that used more resources but still displayed as "better" in batch precentage.

I hope this exercise very well demonstrates that the batch percentage number is not to be taken seriously.
You can always rely on SQL Profiler, which also do not have the bug with INNER JOIN as SET STATISTICS IO ON has.

E 12°55'05.63"
N 56°04'39.26"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-28 : 11:34:57

Peso -- were you still using table variables for your tests with a few thousand debtors? I'm guessing not,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-04-29 : 03:15:27

My grateful thanks to all those who gave their time and knowledge to help me with this.

MANy thanks guys.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-04-29 : 04:02:11

If I have an additional table

payments
--------

date amount debtorid
---- ------ --------
13/2/2008 165.00 123
5/6/2008 10.00 123

how can I join this into the original select statement so that it will return the MIN (last) payment made by the debtor please?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources