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" <[email protected]>
To: <[email protected]>
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" <[email protected]>
> To: <[email protected]>
> 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
>
>