[PHP-DB] 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
Re: [PHP-DB] 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 (a1.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 joefa...@gmail.com To: php-db@lists.php.net Sent: Tuesday, January 13, 2009 6:46 AM Subject: [PHP-DB] 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
Re: [PHP-DB] help me JOIN 3 tables. - fixed query
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.suc...@usa.net To: php-db@lists.php.net Sent: Tuesday, January 13, 2009 7:49 AM Subject: Re: [PHP-DB] 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 joefa...@gmail.com To: php-db@lists.php.net Sent: Tuesday, January 13, 2009 6:46 AM Subject: [PHP-DB] 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