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;

Query on syslogins table

Pls help me out with this query as
select * from master..syslogins WHERE hasaccess = 1
as it is both giving me record where hasaccess = 0 and 1
brgdsThe hasaccess column of the syslogins view is build based on the xstatus column of the sysxlogins table. If you will read carefully the design of thw view you will find the answer.|||Need help with correct SQL string rather than reference as gather fm Books online that hasaccess column is integer

Quote
hasaccess int -- 1, if login has been granted access to the server.
Unquote

hence do not still understand why it is not working ?|||Have you tried this?

select name,hasaccess from master..syslogins where hasaccess=1

I guess problem is QA - just select results in text not in grid.|||select * from sysxlogins where (xstatus&2) = 2
select * from sysxlogins where (xstatus&2) <> 2|||Trust also that's the case