Your problem is in "SELECT e1.*, l1.*": the selected fields have to match the ones in the GROUP BY clause, so something like: SELECT a, b, c, SUM(d) ... GROUP BY a, b, c hth, Yves ------ Original Message ------ Received: Tue, 13 Jan 2009 09:24:48 AM CST From: Abah Joseph <joefazee@xxxxxxxxx> To: Yves Sucaet <yves.sucaet@xxxxxxx> Subject: TAKE A LOOK AT THIS (SQL) SELECT e1.*, l1.*, SUM(a1.adp_amount) as amount FROM e1, l1, a1 WHERE (e1.entre_active = 'Y') AND (l1.entreID = e1.entre_id) AND (l1.loanId = a1.adp_loanID) GROUP BY e1.entre_fname, a1.adp_amount This is a very poor query to me, but so far, it works, but i am still testing different thing, what you gave to me would have been the best and professional but it keep returning error. check the email i sent before this one On Tue, Jan 13, 2009 at 5:55 AM, Yves Sucaet <yves.sucaet@xxxxxxx> wrote: > Oops, actually forgot my GROUP BY clause. The full query is: > > SELECT e1.field1, e1.field2, l1.field3, SUM(a1.adp_amount) as amount > FROM a1 > inner join e1 on (e1.loanID = a1.adp_loanID) > inner join l1 on (l1.entreID = e1.entre_ID) > WHERE e1.entre_active = 'Y' > GROUP BY e1.field1, e1.field2, l1.field3 > > hth, > > Yves > > ----- Original Message ----- From: "Yves Sucaet" <yves.sucaet@xxxxxxx> > To: <php-db@xxxxxxxxxxxxx> > Sent: Tuesday, January 13, 2009 7:49 AM > Subject: Re: help me JOIN 3 tables. > > > Hi Joseph, >> >> With the sum() aggregate function you'll need to use a GROUP BY clause and >> specify which fields you want from e1 and l1. Something like this: >> >> SELECT e1.field1, e1.field2, l1.field3, SUM(a1.adp_amount) as amount >> FROM a1 inner join e1 on (e1.loanID = a1.adp_loanID) inner join l1 on >> (l1.entreID = e1.entre_ID) >> WHERE e1.entre_active = 'Y' >> >> hth, >> >> Yves >> >> ----- Original Message ----- From: "Abah Joseph" <joefazee@xxxxxxxxx> >> To: <php-db@xxxxxxxxxxxxx> >> Sent: Tuesday, January 13, 2009 6:46 AM >> Subject: help me JOIN 3 tables. >> >> >> I have this SQL >>> >>> SELECT e1.*, l1.* FROM e1 >>> INNER JOIN l1 WHERE e1.entre_active = 'Y' AND l1.entreID = e1.entre_id >>> >>> The above query works but i want to add the one below >>> >>> SELECT SUM(a1.adp_amount) as amount FROM a1 WHERE a1.adp_loanID = >>> e1.loanID; >>> >>> the last part of the query is to SUM the part payment made on table 'l1' >>> and >>> return total raised with the first query. >>> >>> the whole idea is three tables, (business, loan, raised), loan referenced >>> ID >>> from business, raised referenced ID from loan. >>> >>> loan maybe $300 and it can be raised over time till completed, so all the >>> amount raised + the loanId will be stored inside the raised table. >>> >>> Thank you >>> >>> >> >> >> -- >> PHP Database Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> >> > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- I develop dynamic website with PHP & MySql, Let me know about your site -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
