bkennedy bkennedy - 11 months ago 45
MySQL Question

nonmonotone SQL queries

I am new to writing SQL queries and am getting stuck on some simple ones, I need to find

drinkers who only like bud

for first one:

SELECT drinker
FROM likes
WHERE beer = 'Bud';


I am aware that the above query will return every drinker that likes bud, I am having trouble transitioning into every drinker that only likes bud.

Answer Source

There are a number of ways to solve this question. I prefer to use the one with not exists subqueries because the subquery in this case does not pull the actual data therefore it is fast:

SELECT drinker
FROM likes l1
WHERE beer = 'Bud'
AND NOT EXISTS (SELECT 1 FROM likes l2 WHERE l2.drinker=l1.drinker and l2.beer <> "Bud");

What it pretty much does is that it selects those drinkers that like Bud and tgen checks if those like anything else. not exists returns true if no records match the condition in the subquery.