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'


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


-------------------------------------
From Japan
Sorry, my English ability is limited.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -