cress cress - 2 months ago 8
MySQL Question

SQL: Bars which every drinker frequents (Using NOT EXISTS)

I am trying to learn how to use

NOT EXISTS
correctly and the logic that goes behind it. I have this query:

Bars which every drinker frequents


Along with this
frequents
table:

enter image description here

Just from looking at the table, I know
Blue Angel
should be the result. I started with something like this (this doesn't give me what I want):

SELECT f.bar
FROM frequents f
WHERE NOT EXISTS(SELECT ff.drinker
FROM frequents ff
WHERE f.drinker <> ff.drinker
AND f.bar = ff.bar
);


So if
f.bar
is "A.P. Stump's", I have to check if every other name in the table also frequents "A.P. Stump's". I can't seem to figure out how to do that.

What would I have to do? Please note, I HAVE TO use
NOT EXISTS
in my query.

Answer

I would do this using group by and having:

select bar
from frequents b
group by bar
having count(*) = (select count(distinct drinker) from frequents);

This assumes that the frequents table doesn't have duplicate rows for bar/drinker. If so, use count(distinct drinker) instead of count(*).

Although this can be phrased as a NOT EXISTS query (actually, two NOT EXISTS would be in the query), I find no use in actually doing it that way. The nested NOT EXISTS is an interesting thought exercise, if you are learning SQL. Aggregation is much more flexible and can answer a much broader range of problems (say, how many bars are frequented by most of the drinkers).

Comments