Re: TAKE A LOOK AT THIS (SQL)

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