Microsoft SQL Query to identify records that have not been created yet-CodePudding

I'm struggling to explain this, so I apologize in advance. I have one table in Microsoft SQL db that contains duplicate records. At some point every duplicate record, should contain at least 1 row where Column C has a value of Yes.

How can I query for records that don't exist yet?

Table t:

| Column A | Column B | Column C |
| -------- | -------- | -------- |
| 1        | Cat      | No       |
| 2        | Cat      | No       |
| 3        | Cat      | No       |
| 4        | Dog      | Yes      |
| 5        | Dog      | No       |
| 6        | Fox      | No       |
| 7        | Fox      | No       |

The expected output looks like:

| Column A | Column B | Column C    |
| -------- | -------- | --------    |
| 1        | Cat      | Missing Yes |
| 2        | Fox      | Missing Yes |

I tried using NOT IN based on examples I found online, but it did not return the desired results.

SELECT Column B
FROM t
WHERE Column C NOT IN
(SELECT Column B
 FROM t
 WHERE Column C = 'Yes'
 )

CodePudding user response：

You can solve this with an common table expression

  WITH cte AS (
  SELECT ColumnA, ColumnB, ColumnC
  FROM t
)

SELECT ColumnA, ColumnB, 'Missing Yes' as ColumnC
FROM cte
GROUP BY ColumnB
HAVING SUM(CASE WHEN ColumnC = 'Yes' THEN 1 ELSE 0 END) = 0

More here