Flow Flow - 2 months ago 6
MySQL Question

Nonmonotonic SQL queries (Finding names who only like something)

I am trying to learn SQL. I have a table (called

likes
) that looks like this:

|---------|---------------|
| name | color |
|---------|---------------|
| Jane | Red |
| Talia | Red |
| Jane | Black |
| Loui | Black |
| John | Black |
| Jane | Green |
| John | Green |
|---------|---------------|


The query is
Names who like only Black
. I tried it this way:

SELECT L1.name FROM likes L1 WHERE L1.color = 'Black'
AND NOT EXISTS (SELECT L2.name FROM likes L2 WHERE L1.name = L2.name);


This returns empty because eventually
Loui
will equal
Loui
in the
NOT EXISTS
sub query. However, I don't know what to change it to so it only returns
Loui
.

I can use
UNION
and everything seen in the query above but nothing else.

Any help would be appreciated!

Answer

I would do this with group by and having:

select name
from likes
group by name
having max(color) = min(color) and max(color) = 'Black';

You can use your method as well. You need a color in the subquery:

SELECT L1.name
FROM likes L1
WHERE L1.color = 'Black' AND
      NOT EXISTS (SELECT L2.name
                  FROM likes L2
                  WHERE L1.name = L2.name AND
                        L2.color <> 'Black'
                 );
Comments