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 , of course I get something like this:

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

What I would like to clarify is:

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

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

In my real example, the resulting LEFT JOIN ed table is in another table, and I want to be able to reject No records, but keep NULL if my other bar table has Num = 4 rows.

Answer 1

Use SUM(condition) in IF :

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

Then your JOIN will become:

 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 

Answer 2

If you have a LEFT JOIN table for yourself, you can easily determine if there are corresponding values ​​or not.

This does the job if you consider the value of "C":

 SELECT t1.num, IF(ISNULL(t2.num),"Yes","No") AS "Has_No_Letter_C" FROM yourtable AS t1 LEFT JOIN yourtable AS t2 ON t1.num=t2.num AND t2.`letter` = "C" GROUP BY num;