CODEWITHSUNDEEP
t-sql
Hanu
Hanu

Reputation: 55

T-SQL Don't select record from a table if one where clause is true

This could be small but I am missing something here. I have a table similar to this

ChildID   EventTypeCode EventypeDescription   DateCompleted
1           4923          HOME VISIT - NURSE    8/22/2016 
1           4954          DEV TEST DONE         8/11/2016  
1           4976          Child Program         8/22/2016
2           4923          HOME VISIT - NURSE    9/22/2016 
2           4954          DEV TEST DONE         9/11/2016  
2           4976          Child Program         9/22/2016
3           4954          DEV TEST DONE         10/22/2016

My criteria to select records is "no event codes titled “Home Visit – Nurse” that have a completion date"

Select ChildID from [Test]
where (Eventypecode not in (4923) and DateCompleted is not Null)

I should Only get ChildID 3 but still I am getting Child 1,2. I checked by putting EventTypeCode in query where i found it's selecting ChildID's 1 and 2 for other eventypecodes(4954 and 4976) So I dont want records if it has one record with 4923 Eventype.

I tried Sub query Not in and Not exists didn't work out

Upvotes: 0

Views: 42

Answers (2)

JNevill
JNevill

Reputation: 50034

I think you want something like:

SELECT distinct childid
FROM [Test]
WHERE childid NOT IN (SELECT childid FROM [Test] WHERE EventTypeCode = 4923 OR DateCompleted IS NULL);

That is, you don't want to return any ChildID when ANY of it's records have a 4923 OR a NULL date. You'll have to do that logic in a subquery.

SQLFiddle here

Upvotes: 2

paparazzo
paparazzo

Reputation: 45096

Question is not clear
? 

SELECT distinct childid
FROM [Test]
WHERE childid NOT IN (SELECT childid 
                      FROM [Test] 
                      WHERE EventTypeCode = 4923 
                      AND   DateCompleted is not Null);

Upvotes: 0

Related Questions