CODEWITHSUNDEEP
mysqlsqlquerying
Bret
Bret

Reputation: 159

SQL Query Using Minus/Not Exists

I want to query this statement:

Drinkers who do not frequent the bar Henrys

Now, I know I cant use a simple query like this:

SELECT dinker
FROM frequents
WHERE bar <> 'Henrys';

because what if a person goes to Henrys bar and also frequents other bars as well? This person will still be included in the result. This query only filters out the people who only frequent Henrys bar and nothing else.

I tried to use the MINUS operator, but in MySQL it doesn't seem to support the query:

SELECT drinker 
FROM frequents
MINUS
SELECT drinker
FROM frequents
Where bar = 'Henrys';

I want to try and rewrite this using NOT EXISTS, but can't seem to get it to work or correctly think through the logic.

Upvotes: 0

Views: 2438

Answers (2)

Chris Smith
Chris Smith

Reputation: 21

Something to the effect of finding all of the drinkers that went to a Henry's Bar and then selecting the drinkers that are not part of that array

SELECT drinker
FROM frequents
WHERE drinker NOT IN (SELECT drinker FROM frequents WHERE bar = 'Henrys')

Upvotes: 2

Brian DeMilia
Brian DeMilia

Reputation: 13248

select drinker
  from frequents f
 where not exists (select 1
          from frequents x
         where x.drinker = f.drinker
           and x.bar = 'Henrys');

Upvotes: 2

Related Questions