Sql query - 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)

Sql query

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2006-07-04 : 10:25:05

I have following sql tables(All the blue word indicates primary key)

batch-->batchid
batchpaymentdetails with columns -->batchid,paidamount,paymentid

payment--->paymentid
invoiceheader-->invoiceno,paymentid,candidateid

candidate-->candidateid
candidatecourse--->candidateid, iscanceladmission(bit)

Now i write following query

select ih.invoiceno from candidatecourse cc,invoiceheader ih
where cc.candidateid=ih.candidateid and cc.isCancelAdmission=0 and cc.candidateid=48 and cc.batchID=10 gives the correct output what i want

but now i have to calulate the paidamount from the batchpayment for the same above correct output

so i have written the query

select batchPaidAmount from BatchPaymentDetails where paymentID in (select paymentID from payment where invoiceNo in(select ih.invoiceno from candidatecourse cc,invoiceheader ih
where cc.candidateid=ih.candidateid and cc.isCancelAdmission=0 and cc.candidateid=48 and cc.batchID=10))

this query is not giving the output according to query in green color

as i want the output for the paidamount with isCancelAdmission=0 but it is giving for both (0 and 1)

Plz tell me how can i use joins or any other solution?

Swati

wernerdejong
Starting Member

7 Posts

Posted - 2006-07-04 : 10:40:12

I can only recommend to write a stored procedure that uses select into tmp tables. Makes it visible what the in statements return. Then work your way back up to the one query result.

(p.s. an in statement is very resource hungry so be carefull with em.)

mr_mist
Grunnio

1870 Posts

Posted - 2006-07-04 : 10:55:24

select ih.invoiceno, SUM(paidamount)
from candidatecourse cc
INNER JOIN invoiceheader ih
ON cc.candidateid=ih.candidateid
LEFT OUTER JOIN batchpaymentdetails dpd
ON cc.paymentid = bpd.paymentid
where cc.isCancelAdmission=0 and cc.candidateid=48 and cc.batchID=10
GROUP BY ih.invoiceno

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources