[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

need help with query

Posted on 2013-05-17
12
Medium Priority
?
241 Views
Last Modified: 2013-05-22
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
0
Comment
Question by:sqlcurious
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 664 total points
ID: 39176261
Give this a whirl...

SELECT DISTINCT yt1.customer_no, yt1.emailAddress
FROM YourTable yt1
WHERE Deleted = 'D'
JOIN (
   SELECT DISTINCT customer_no, emailAddress
   FROM YourTable
   WHERE Deleted = '') yt2 ON yt1.customer_no = yt2.customer_no AND yt1.email_address = yt2.email_address
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39176268
check this out

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

Open in new window

0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 668 total points
ID: 39176401
Although you asked for the reverse of what I'm suggesting here, I assume you are trying to find the records without the 'D' so they are either removed or to have the Deleted field set to 'D'. If this is the case then this is similar to Jim's suggestion but with a couple of tweaks. The inner subquery finds those records with Deleted = 'D' so that the final result is of those records of Deleted = ''. And; the where clause is moved under the from clause.
SELECT
      c1.customer_no
    , c1.emailAddress
FROM Customers c1
INNER JOIN (
            SELECT DISTINCT
                  customer_no
                , emailAddress
            FROM Customers
            WHERE Deleted = 'D'
            ) c2 ON c1.customer_no = c2.customer_no
                    AND c1.email_address = c2.email_address
WHERE c1.Deleted = ''
    -- or c1.Deleted is null

Open in new window

although you only asked for '' in the filtering I'd suggest you also look for nulls in the Deleted field.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 41

Expert Comment

by:Sharath
ID: 39178673
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
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 668 total points
ID: 39181904
SELECT
    emailAddress,
    MIN(customer_no) AS minCustomer,
    MAX(customer_no) AS maxCustomer
FROM
    dbo.tablename
WHERE
    Deleted IN ( '', 'D' )
GROUP BY
    emailAddress
HAVING
    MIN(Deleted) = '' AND
    MAX(Deleted) = 'D'
ORDER BY
    emailAddress
0
 

Author Comment

by:sqlcurious
ID: 39185861
For some reason none of the queries above are working :( any idea as to what's going on?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39185875
Define 'not working'.  Is there an error message?  No return records?  
Does the 'deleted flag' and email address values have any trailing spaces?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39186062
"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

0
 

Author Comment

by:sqlcurious
ID: 39187286
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
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39187574
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.
0
 

Author Comment

by:sqlcurious
ID: 39187852
yes Scott's worked, guess I was doing something wrong with the group by earlier, thanks !
0
 

Author Closing Comment

by:sqlcurious
ID: 39187858
Thanks!
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question