SQL Return only rows that do not contain multiple values in column

Question

Take the below table

Col 1	Col2
aa	null
aa	date
aa	null
bb	date
cc	null
cc	null
dd	date
dd	date

What I'm looking to return would be the below where col 1 is returned only if col 2 is date only rows with null and date or just null removed.

Col 1	Col2
bb	date
dd	date
dd	date

Answer 1

I think this is what you want:

SELECT Col1, Col2
FROM yourTable
WHERE Col1 IN (
    SELECT Col1
    FROM yourTable
    GROUP BY Col1
    HAVING COUNT(CASE WHEN Col2 = 'date' THEN 1 END) = COUNT(*)
);

The above logic ensures that any matching Col1 value has only date appearing in Col2.

Answer 2

This is based on the answer in the question I closed this as a duplicate of, Select Only Those rows which occur exactly once, but uses a conditional COUNT instead:

WITH CTE AS(
    SELECT Col1,
           Col2,
           COUNT(CASE Col2 WHEN 'date' THEN NULL ELSE 1 END) OVER (PARTITION BY Col1) AS NonDateCount
    FROM dbo.YourTable)
SELECT Col1,
       Col2
FROM CTE
WHERE NonDateCount = 0;

db<>fiddle

Answer 3

As per your table structure please try the below queries:

SELECT * FROM yourtable WHERE col2 IS NOT NULL

or Col1 records having date with counts:

WITH cte AS (
  SELECT col1, col2
  FROM YOURTABLE
)
SELECT col1,col2, COUNT(*)
FROM YOURTABLE
WHERE col2 IS NOT NULL AND col2 NOT LIKE '%null%' 
GROUP BY col1
HAVING COUNT(*) > 0

Col 1	Col2
bb	date
dd	date
dd	date

Answer 4

Based on the expected outputs, it looks like you're essentially trying to capture all instances of a group with 0 null subgroups. You could use count if there was a target number other than 0 but if the requirement is 0 you can do this.

SELECT YT.col1,
       YT.col2
FROM YOURTABLE AS YT
WHERE NOT EXISTS(
   SELECT 1 
   FROM YOURTABLE AS YTI
   WHERE YT.COL1  = YTI.Col1 
   AND YTI.Col2 IS NULL
)