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

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

Use of exists in where clause

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Use of exists in where clause

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us