Link to home
Create AccountLog in
Avatar of sqlcurious
sqlcuriousFlag for United States of America

asked on

need help with query

Hi experts,
I have a situation where I should find customers who have a deleted flag 'D' and blank ' ' for the same email address. Please find below example:

customer_no  | emailAddress  | Deleted
 1111                ap@gmail.com       D
 2221                bp@gmail.com       D
 1111                ap@gmail.com      
 3333                cp@gmail.com
SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
check this out

selecet * from YourTable
where Delted = 'D'
AND emailAddress  = ''

Open in new window

SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
try this.
SELECT customer_no, 
       emailAddress 
  FROM test 
 WHERE Deleted IN ( '', 'D' ) 
 GROUP BY customer_no, 
          emailAddress 
HAVING COUNT(*) = 2 
       AND MIN(Deleted) = '' 
       AND MAX(Deleted) = 'D' 

Open in new window


see the example here: http://sqlfiddle.com/#!3/e1153/2
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of sqlcurious

ASKER

For some reason none of the queries above are working :( any idea as to what's going on?
Define 'not working'.  Is there an error message?  No return records?  
Does the 'deleted flag' and email address values have any trailing spaces?
"not working" does not give us much to work with and it would be good to know what the symptoms really are. Perhaps try the following and provide the results?
SELECT 'c1.Deleted ""' AS filterfor
	, count(*) AS counted
FROM Customers c1
WHERE c1.Deleted = ''

UNION ALL

SELECT 'c1.Deleted "" or null' AS filterfor
	, count(*) AS counted
FROM Customers c1
WHERE c1.Deleted = ''
	OR c1.Deleted IS NULL

UNION ALL

SELECT 'c1.Deleted not "D" or null' AS filterfor
	, count(*) AS counted
FROM Customers c1
WHERE c1.Deleted NOT = 'D'
	OR c1.Deleted IS NULL

UNION ALL

SELECT 'Deleted = "D"' AS filterfor
	, count(*) AS counted
FROM Customers
WHERE Deleted = 'D'

UNION ALL

SELECT DISTINCT 'Deleted like "D%"' AS filterfor
	, count(*) AS counted
FROM Customers
WHERE Deleted LIKE 'D%';

Open in new window

Hi all, I am sorry about not being specific, @Jimhorn I wasnt getting any results.
@PortletPaul please find the results below for the query you suggested:

filterfor                       Counted
c1.Deleted ""      1752036
c1.Deleted "" or null      1752036
c1.Deleted not "D" or null      1752037
Deleted = "D"      1734839
Deleted like "D%"      1734839

Pls suggest, thanks
sqlcurious, thanks very much for the results. they show that the data is very much as you explain it - so I'm really not sure why you are not get results from any of the queries.

did you try the one from Scott? (ID: 39181904) using an aggregation may be just the ticket.
yes Scott's worked, guess I was doing something wrong with the group by earlier, thanks !
Thanks!