Link to home
Start Free TrialLog in
Avatar of Jasmin01
Jasmin01Flag 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?
ASKER CERTIFIED SOLUTION
Avatar of chaau
chaau
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

@PortletPaul: what if they need to retrieve other columns alongside with id
>>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)
Oh well it appears the debate is academic.

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