NOT EXISTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NOT EXISTS

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-02-25 : 10:48:13

I am trying to run a query to find
GBOBJ values with a totalamount not equal to zero in the F0902 table
but also do not exist in the L5Accounts column in the D_Accounts table.

The following does not quite work, thank you for your assistance:

select distinct GBOBJ,SUM(totalamount) as total from F0902 where GBCO = 01003 and GBFY = 10 and GBLT = 'AA' and TotalAmount <>0 group by gbobj
WHERE GBOBJ NOT IN (SELECT L5ACCOUNT from D_Accounts where LEID = 01003)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-25 : 11:01:17

Here's a version using NOT EXISTS:

SELECT GBOBJ, SUM(totalamount) AS total
FROM F0902
WHERE GBCO = 01003 AND GBFY = 10 AND GBLT = 'AA' AND TotalAmount <> 0
AND NOT EXISTS(SELECT * FROM D_Accounts WHERE L5ACCOUNT=F0902.GBOBJ AND LEID = 01003)
GROUP BY GBOBJ

tariq2
Posting Yak Master

125 Posts

Posted - 2011-02-25 : 11:20:00

Robvolk,

Thats great.

Thank you :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources