help me JOIN 3 tables.

--00c09fa887cd3c4e5004605c9d0c
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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

--00c09fa887cd3c4e5004605c9d0c--

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 (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"
To:
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: 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"
To:
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"
> To:
> 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