SIMPLE PROBLEM WITH A JOIN - 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 2000 Forums

SQL Server Development (2000)

SIMPLE PROBLEM WITH A JOIN

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-10-18 : 10:51:02

I have 4 tables

Projects, Requests, Project_Request_link, payments

Create table #projects (projectid int, projectname char(12))
insert #projects
select 1, 'AAA' union all
select 2, 'BBB' union all
select 3, 'CCC'

Create table #requests (requestno int, requestname char (10), amount int)
insert #requests
select 111, 'first' , 10000 union all
select 222, 'second', 20000 union all
select 333, 'third' , 30000 union all
select 444, 'fourth' , 40000

create table #payments (requestno int , invoiceamount int, paymentamount int)
insert #payments
select 111, 1000, 1000 union all
select 222, 2000, 2000 union all
select 333, 3000, 3000 union all
select 444, 4000, 4000 union all
select 111, 1111, 1111 union all
select 222, 2222, 2222 union all
select 111, 3333, 3333

create table #Project_Request_link (projectid int, requestno int)
insert #Project_Request_link
select 1, 111 union all
select 2, 222 union all
select 3, 333 union all
select 1, 444

My query need to see the projectname, sum(amount) from requests table
total invoiced for that project and total paid for that project

EACH PROJECT CAN HAVE ONE OR MORE REQUESTS. Each request will have one or more payments made.

select

pr.projectname,
sum(r.amount) as 'TOTAL AMOUNT',
sum(py.invoiceamount) 'TOTAL INVOICE',
sum(py.paymentamount)'TOTAL PAID'

FROM
#projects pr,
#requests r,
#payments py,
#Project_Request_link prl
where

r.requestno = py.requestno and
prl.projectid = pr.projectid and
r.requestno = prl.requestno

group by pr.projectname

NOW SINCE project AAA has total amount of 50,000 and not 70,000
i am missing something here. I tried all kinds of joind but it does not works. Since for project AAA request 111 is repeating 3 times in table payments it adds up 3 times for the amount of 10,000 instead of just once. Similarly for project BBB I am getting 40,000 in amount instead of 20,000
ANY WAY TO AVOID THIS. DATABASE DESIGN CAN NOT BE CHANGED.

Ashley Rhodes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-18 : 11:02:03

- Can you explain the relationship between the tables ?
- What is the amount in requests table for ?
- Why is requestno 111 repeated 3 times in payments table ?

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-10-18 : 11:06:49

Each Project can have multiple requests for their need. They will use the request amount
to pay for the stuff they buy in installments. That is why its repeated 3 times. Maybe I should have changed the invoice amount and payment amount to avoid confustion. So for each request the money they spend will be invoiced and paid using the Payments table.

You can have another column in payments called Vendor. Means where did they buy that stuff from. They can use the money from the request in installments but to know what request money is coming from there is requestno in payments table as well.

Ashley Rhodes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-18 : 11:24:28

Try this.


select	a.projectname, a.[TOTAL AMOUNT], b.[TOTAL INVOICE], b.[TOTAL PAID]
from
(
	select	p.projectid, p.projectname,
		sum(r.amount) as [TOTAL AMOUNT]
	from	#projects p inner join #Project_Request_link l
		on	p.projectid	= l.projectid
		inner join #requests r
		on	l.requestno	= r.requestno
	group by p.projectid, p.projectname
) a
inner join
(
	select	n.projectid, 
		sum(p.invoiceamount) as [TOTAL INVOICE], 
		sum(paymentamount) as [TOTAL PAID]
	from	#Project_Request_link n inner join #payments p
		on	n.requestno	= p.requestno
	group by n.projectid
) b
on	a.projectid	= b.projectid

Hope i understand you correctly

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-10-18 : 11:35:44

THANKS YEAH THIS WORKS

Ashley Rhodes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources