Difficulty with Where Clause - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Difficulty with Where Clause

Author

Topic

dutchgold647
Starting Member

13 Posts

Posted - 2014-10-09 : 22:11:18

hi all,

hoping someone can help me here.

i'm running a simple query that i can't figure out.

it should return all accounts where a note in column = 'ID Reject'
AND where another note in the same column = 'Address Reject'

Below is the query that i am using:

SELECT Account
,Note

FROM AddNote

WHERE Note = 'ID Reject'
AND Note = 'Address Reject'

the problem is that it either returns nothing or everything - i just need it to return each account where both those notes have been entered.

also please be aware that using or statement doesn't work either because accounts will exist in the database that will have one of these notes entered but not the other one.

thanks everyone

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-10-10 : 03:08:52

Try

SELECT
  Account
 ,Note
FROM AddNote
WHERE Note = 'ID Reject'
OR Note = 'Address Reject'

SELECT
  Account
 ,Note
FROM AddNote
WHERE Note IN ('ID Reject', 'Address Reject')

-------------------------------------
From Japan
Sorry, my English ability is limited.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-13 : 12:41:02

quote:
i just need it to return each account where both those notes have been entered


SELECT Account
FROM AddNote
WHERE Note = 'ID Reject' 
OR Note = 'Address Reject'
GROUP BY Account
HAVING MAX(CASE WHEN Note = 'ID Reject' THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN Note = 'Address Reject' THEN 1 ELSE 0 END) = 1

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-10-13 : 15:53:44

This will give you where both 'ID Reject' and 'Address Reject' are in the same column.

SELECT Account, Note
FROM AddNote 
WHERE Note LIKE '%ID Reject%Address Reject%';

Or if you mean one record has 'ID Reject' and a second record for the same account has 'Address Reject' then one way would be

WITH cte AS (SELECT Account FROM AddNote WHERE Note = 'ID Reject') 
SELECT A.Account 
FROM AddNote A 
INNER JOIN cte ON A.Acount = cte.Account 
WHERE A.Note = 'Address Reject';

djj

dutchgold647
Starting Member

13 Posts

Posted - 2014-10-13 : 22:32:46

hi all, sorry for not getting back to you sooner.

thank you for solving my problem - the one that worked best was ScottPletcher

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources