Use of exists in where clause

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi Friends,
I have 2 table

1. Account<br />
2. Closed Account

I want list of account which is not closed.

I have tried following

SELECT distinct  vwA.AccountID,vwA.AccountName                         
  FROM vwAccounts  vwA       
  inner JOIN RMT_RiskAssessmentPlan AP ON AP.Accountcode = VWA.AccountID                        
  WHERE AP.[Year] = 2013 AND   
        VWA.AccountID NOT IN (SELECT AccountCode FROM RMT_ClosedAccount WHERE [YEAR] = 2013)            
  order by vwA.AccountName

How can i replace the in clause with exists in following query?

I tired following

SELECT distinct  vwA.AccountID,vwA.AccountName
 FROM vwAccounts  vwA
 inner JOIN RMT_RiskAssessmentPlan AP ON AP.Accountcode = VWA.AccountID
 WHERE AP.[Year] = 2013 AND not exists(SELECT AccountCode FROM RMT_ClosedAccount WHERE [YEAR] = 2013)
 order by vwA.AccountName

But it returned me 0 result

Any idea how to use the exists clause?

Thanks in advance

Posted 26-Sep-13 23:24pm

dhage.prashant01

Updated 26-Sep-13 23:30pm

v2

Add a Solution

2 solutions

Solution 1

Based on the query you posted, the below query should give you the desired result-

SELECT distinct vwA.AccountID,vwA.AccountName
FROM vwAccounts vwA
inner JOIN RMT_RiskAssessmentPlan AP ON AP.Accountcode = VWA.AccountID
WHERE AP.[Year] = 2013 AND
NOT EXISTS(SELECT * FROM RMT_ClosedAccount WHERE [YEAR]=AP.[YEAR])
order by vwA.AccountName

Posted 26-Sep-13 23:27pm

Madhu Nair

Comments

dhage.prashant01 27-Sep-13 5:31am

I have update my question.
Not exists gives me 0 record and if i use exists it gives me closed as well as open account

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Madhu Nair · Accepted Answer · 2013-09-26T23:35:00

Solution 2

Sorry .... i missed one condition. Try the below query -

SELECT distinct vwA.AccountID,vwA.AccountName
FROM vwAccounts vwA
inner JOIN RMT_RiskAssessmentPlan AP ON AP.Accountcode = VWA.AccountID
WHERE AP.[Year] = 2013 AND
NOT EXISTS(SELECT * FROM RMT_ClosedAccount WHERE [YEAR]=AP.[YEAR] and ClosedAccount.AccountID=AP.AccountID)
order by vwA.AccountName

Posted 26-Sep-13 23:35pm

Madhu Nair

Comments

dhage.prashant01 27-Sep-13 5:46am

How does it actually works??For every account in RMT_RiskAssessmentPlan it checks if ti closed or not.If it is not closed then that account is returned else it is skiped.Correct me if I'm wrong..

Madhu Nair 27-Sep-13 5:47am

Is it working?

dhage.prashant01 27-Sep-13 5:50am

Yes it working. Actually I was just checking to optimise the query. But both IN and EXISTS Query Cost is 100%

Madhu Nair 27-Sep-13 5:51am

With me EXISTS always performed better than IN Queries. Please marked the question as answered which may help others also