-1

I am trying to query this statement:

 Drinkers who frequent all bars

Now I have a table called bars with a column of the names of every bar.

I also have a table called frequents with the name of the drinker and which bars they frequent.

With one query I was able to find the number of distinct bars from the bars table by using the count operator:

SELECT COUNT(DISTINCT name) AS num_bars 
FROM bars;

The answer was 18.

With a separate query I could then check the frequents table, seeing which drinker frequents a total of 18 bars follows:

SELECT drinker
FROM frequents
GROUP BY drinker
HAVING COUNT(drinker) = 18;

First, I am not sure how to combine these 2 queries into one (the join process is slightly confusing me). Second, I am not sure if my logic is completely sound. What if a drinker frequents a bar that isn't in the bars table (which doesn't happen in my data set)? Then my two query system would fail.

Bret
  • 167
  • 1
  • 1
  • 12

1 Answers1

0

You don't need a join (although you could use one). Just use a subquery in the having clause:

SELECT drinker
FROM frequents
GROUP BY drinker
HAVING COUNT(drinker) = (SELECT COUNT(DISTINCT name) FROM bars);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786