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!

score 1 · Accepted Answer · answered Feb 20 '13 at 18:36

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.

Thank you! Using UNION and HAVING work out perfectly. Cheers. — BenR, Feb 20 '13 at 18:48

score 0 · Answer 2 · answered Feb 20 '13 at 18:24

I don't have a database to hand, but you should be able to use a query along the lines of.

SELECT name from FLAVOURS WHERE flavour IN ( SELECT flavour, count(Flavour) from FLAVOURS where count(Flavour) = 1 GROUP BY flavour) OR flavour IS NULL;

I apologise if this isn't quite right, but hopefully is a good start.

score 0 · Answer 3 · answered Feb 20 '13 at 18:38

You need a self-join that looks for duplicates, and then you need to veto those duplicates by looking for cases where there was no match (that's the WHERE t2.flavor IS NULL). Then your're doing something completely different, looking for nulls in the original table, with the second line in the WHERE clause (OR t1.flavor IS NULL)

SELECT DISTINCT t1.name, t1.flavor 
FROM tablename t1
LEFT JOIN tablename t2
ON t2.flavor = t1.flavor AND t2.ID <> t1.ID
WHERE t2.flavor IS NULL
OR t1.flavor IS NULL

I hope this helps.

MySQL Select if field is unique or null

3 Answers3