Hide rows when column values are duplicate

Question

I have a table named "letters" with two columns looking like this:

case_nr | date
--------+-----------------------
1       | 2015-06-13 12:45:04
1       | NULL
2       | 2015-06-11 12:45:09
3       | 2015-06-12 17:41:49
3       | 2015-06-13 18:42:99

case_nr 1 have printed 2 letters but only one was sent

I want to filter all cases where all letters was sent (have a date)

So in this case result should be:

2
3

Answer 1

You can use DISTINCT with NOT IN:

SELECT DISTINCT case_nr
FROM TableName
WHERE case_nr NOT IN 
          (SELECT case_nr FROM TableName WHERE [date] IS NULL )

Result:

case_nr
--------
2
3

Sample result in SQL Fiddle.

Answer 2

Group by the case_nr and take only those having no record with date is null

select case_nr
from your_table
group by case_nr
having sum(case when date is null then 1 else 0 end) = 0

SQLFiddle demo

Answer 3

As an alternative:

SELECT 
    case_nr
FROM (
    SELECT
        case_nr,
        COUNT(CASE WHEN [date] IS NULL THEN 1 END) AS cnt
    FROM 
        yourTable
    GROUP BY
        case_nr
    ) t
WHERE
    cnt = 0