MySQL Select if field is unique or null

Question

Sorry, I can't find an example anywhere, mainly because I can't think of any other way to explain it that doesn't include DISTINCT or UNIQUE (which I've found to be misleading terms in SQL).

I need to select unique values AND null values from one table.

FLAVOURS:
id  |  name  |  flavour
--------------------------
1   |  mark  |  chocolate
2   |  cindy |  chocolate
3   |  rick  |  
4   |  dave  |  
5   |  jenn  |  vanilla
6   |  sammy |  strawberry
7   |  cindy |  chocolate
8   |  rick  |  
9   |  dave  |  
10  |  jenn  |  caramel
11  |  sammy |  strawberry

I want the kids who have a unique flavour (vanilla, caramel) and the kids who don't have any flavour.

I don't want the kids with duplicate flavours (chocolate, strawberry).

My searches for help always return an answer for how to GROUP BY, UNIQUE and DISTINCT for chocolate and strawberry. That's not what I want. I don't want any repeated terms in a field - I want everything else.

What is the proper MySQL select statement for this?

Thanks!

Answer 1

You can use HAVING to select just some of the groups, so to select the groups where there is only one flavor, you use:

SELECT * from my_table GROUP BY flavour HAVING COUNT(*) = 1

If you then want to select those users that have NULL entries, you use

SELECT * FROM my_table WHERE flavour IS NULL

and if you combine them, you get all entries that either have a unique flavor, or NULL.

SELECT * from my_table GROUP BY flavour HAVING COUNT(*) = 1 AND flavour IS NOT NULL
UNION
SELECT * FROM my_table WHERE flavour IS NULL

I added the "flavour IS NOT NULL" just to ensure that a flavour that is NULL is not picked if it's the single one, which would generate a duplicate.