CODEWITHSUNDEEP
sqlt-sql
Mr.B
Mr.B

Reputation: 397

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

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

Upvotes: 0

Views: 83

Answers (4)

Kevin Johnson
Kevin Johnson

Reputation: 218

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
)

Upvotes: 0

Ikkhan
Ikkhan

Reputation: 16

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

enter image description here

Col 1 Col2
bb date
dd date
dd date

Upvotes: 0

Thom A
Thom A

Reputation: 95561

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

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.

Upvotes: 3

Related Questions