Query problem

Need answer pls to this problem:
Select charge_id,name,amount from tblcharge t inner join
tblpayments p on p.id = t.id where p.charge_id not in
(select t.charge_id from from tblcharge t inner join
tblpayments p on p.id = t.id where t.amount - p.amount = 0)
This works if data is:
charge_id name amount
1 Johnson -50.00 (from payment table)
1 Johnson 50.00 (from charge table)
but not if
charge_id name amount
1 Johnson -38.60 (from payment table)
1 Johnson -11.40 (from payment table)
1 Johnson 50.00 (from charge table)
Essentially not working if two or more payments were
received.
Pls. help ASAP. Thanks.Select charge_id,name,amount from tblcharge t inner join
tblpayments p on p.id = t.id where p.charge_id not in
(select t.charge_id from from tblcharge t inner join
tblpayments p on p.id = t.id where t.amount - sum(p.amount) = 0
group by t.charge_id )
see if this helps you, don't have the complete DDL so don't know if it is
100% correct
"Merwin12" <anonymous@.discussions.microsoft.com> wrote in message
news:152b01c530b0$8cba8ae0$a601280a@.phx.gbl...
> Need answer pls to this problem:
> Select charge_id,name,amount from tblcharge t inner join
> tblpayments p on p.id = t.id where p.charge_id not in
> (select t.charge_id from from tblcharge t inner join
> tblpayments p on p.id = t.id where t.amount - p.amount = 0)
> This works if data is:
> charge_id name amount
> 1 Johnson -50.00 (from payment table)
> 1 Johnson 50.00 (from charge table)
> but not if
> charge_id name amount
> 1 Johnson -38.60 (from payment table)
> 1 Johnson -11.40 (from payment table)
> 1 Johnson 50.00 (from charge table)
> Essentially not working if two or more payments were
> received.
> Pls. help ASAP. Thanks.|||Try,
Select
t.charge_id,
max(t.[name]),
t.amount
from
tblcharge t
left join
tblpayments p
on p.id = t.id
group by
t.charge_id
having
sum(t.amount) != isnull(sum(p.amount), 0)
go
AMB
"Merwin12" wrote:

> Need answer pls to this problem:
> Select charge_id,name,amount from tblcharge t inner join
> tblpayments p on p.id = t.id where p.charge_id not in
> (select t.charge_id from from tblcharge t inner join
> tblpayments p on p.id = t.id where t.amount - p.amount = 0)
> This works if data is:
> charge_id name amount
> 1 Johnson -50.00 (from payment table)
> 1 Johnson 50.00 (from charge table)
> but not if
> charge_id name amount
> 1 Johnson -38.60 (from payment table)
> 1 Johnson -11.40 (from payment table)
> 1 Johnson 50.00 (from charge table)
> Essentially not working if two or more payments were
> received.
> Pls. help ASAP. Thanks.
>|||Correction,
Select
t.charge_id,
max(t.[name]),
max(t.amount)
from
tblcharge t
left join
tblpayments p
on p.id = t.id
group by
t.charge_id
having
sum(t.amount) != isnull(sum(p.amount), 0)
go
AMB
"Alejandro Mesa" wrote:
> Try,
> Select
> t.charge_id,
> max(t.[name]),
> t.amount
> from
> tblcharge t
> left join
> tblpayments p
> on p.id = t.id
> group by
> t.charge_id
> having
> sum(t.amount) != isnull(sum(p.amount), 0)
> go
>
> AMB
> "Merwin12" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. And stop using that sillly redundant "tbl-" -- this is
SQL, not 1960's BASIC; read ISO-11179 for the rules on data element
names.
You also neve said what you wanted to do, but only posted code that was
not working right. I will further guess that you want the people who
have paid off their charges:
SELECT X.charge_id, X.name
FROM ( SELECT charge_id, name, amt
FROM Charges
UNION ALL
SELECT charge_id, name, amt
FROM Payments) AS X(charge_id, name, amt)
GROUP BY X.charge_id, X.name
HAVING SUM(amt) = 0.00;