How to determine if a value appears in a GROUP BY group

Question

Given the table foo:

Num   Letter
------------
1     A
1     B
1     B
2     C
3     A
3     C
3     D

If I do SELECT * GROUP BY Num I of course get something like this:

Num   Letter
------------
1     A
2     C
3     A

What I'd like to refine this to is:

Num   Has_No_Letter_C
---------------------
1     Yes
2     No
3     No

There's probably a simple SELECT IF () plus a ORDER BY, but I can't see it right now...

In my real-world example, the resultant table is LEFT JOINed to another table and I want to be able to reject the No entries but keep the NULLs if my other table bar has a Num = 4 row.

Answer 1

Use SUM(condition) within the IF:

SELECT   Num,
         IF(SUM(Letter = 'C'), 'Yes', 'No') AS Has_Letter_C
FROM     my_table
GROUP BY Num

Your JOIN then becomes:

SELECT   another_table.Num
FROM     another_table LEFT JOIN my_table ON another_table.Num = my_table.Num
GROUP BY another_table.Num
HAVING   my_table.Num IS NULL OR SUM(my_table.Letter = 'C') > 0