cress cress - 1 year ago 91
MySQL Question

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

I am trying to learn how to use

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

Bars which every drinker frequents

Along with this

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):

FROM frequents f
FROM frequents ff
WHERE f.drinker <> ff.drinker

So if
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
in my query.

Answer Source

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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download