I have a problem with the GROUP BY clause in SQL 2000.
I have 3 tables in db:
1. INVESTORS with fields (idinvestor, name, address etc) ,
2. CONTRACTS with fields (idcontract, idinvestor, contnr, date, etc.) and
3. PAYMENTS with fields (idpayment, idcontract, dateofpayment, amount etc.).
I want to make a report of payments and group them by idcontract, like this :
SELECT idpayment, idcontract, dateofpayment, SUM(amount) FROM PAYMENTS GROUP BY idcontract
but I would like to join tables CONTRACTS and INVESTORS also, so I can have number of the CONTRACT (contnr) and name of the INVESTOR (name) in the same report.
I don't know should I use nested query or JOIN command and if anyone has any example...... ?
SELECT P.idpayment, P.idcontract, P.dateofpayment,
SUM(P.amount),I.Name, C.Contnr FROM PAYMENTS P, Investors I ,
Contracts C where P.idContract=C.idContract and C.idinvestor=I.idInvestor
GROUP BY P.idpayment, P.idcontract, P.dateofpayment, I.Name, C.Contnr