Avatar of Jasmin01
Jasmin01
Flag for South Africa asked on

SQL - query to remove ID that has been contacted

I have a table in a SQL database that stores the number of times a client is contacted.

It looks similar to this:

ID           Date               Status
1001       2014-05-01     Not Contacted
1001       2014-05-02     Not Contacted
1001       2014-05-04     Contacted
1005       2014-05-04     Not Contacted
1005       2014-05-05     Contacted
1007       2014-05-07     Not Contacted
1007       2014-05-08     Not Contacted
1009       2014-05-08     Not contacted

I want to select only those ID's that have not been contacted, so if you look at my example. ID 1001 has been attempted 3 times. but since t has been contacted, I want to exclude it.  ID 1007 and 1009 have not been contacted so those are the only 2 I want to return.  Any ideas on how to do this?
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
chaau

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PortletPaul

A more efficient approach requiring just a single pass of the data is like this
SELECT
        id
FROM Contacts
GROUP BY
      id
HAVING
      count(CASE WHEN Status = 'Contacted' THEN 1 END) = 0
;

--result
|   ID |
|------|
| 1007 |
| 1009 |
	

Open in new window

chaau

@PortletPaul: what if they need to retrieve other columns alongside with id
PortletPaul

>>from the question: "I want to select only those ID's that have not been contacted"
extra columns aren't requested, and probably not needed

Let's assume I haven't contacted you for the last month; do I really need 28/29/30 or 31 records to tell me I haven't contacted you? :-)

The group by approach could return min(date)/max(date) which might be useful (perhaps)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
PortletPaul

Oh well it appears the debate is academic.

@Jasmin01 if you do only want the ID, then my suggestion is more efficient (faster)