# Nonomonotone mysql queries (Using NOT EXISTS)

Go to solution Solved by Barand,

## Recommended Posts

Hello! I need help making a query for this statement:

`Drinkers who frequent exactly one of the bars which Joe frequents`

The table looks like this:

```---------------------------------------------
| drinker | bar                             |
---------------------------------------------
| Mike    |A.P. Stump's                     |
| Bob     |Blue Angel                       |
| Erik    |Blue Angel                       |
| Herb    |Blue Angel                       |
| Jesse   |Blue Angel                       |
| Joe     |Blue Angel                       |
| John    |Blue Angel                       |
| Justin  |Blue Angel                       |
| Mike    |Blue Angel                       |
| Rebecca |Blue Angel                       |
| Tom     |Blue Angel                       |
| Vince   |Blue Angel                       |
| John    |Cabana                           |
| Mike    |Cabana                           |
| Vince   |Cabana                           |
| Joe     |Caravan                          |
| John    |Caravan                          |
| Tom     |Caravan                          |
| Bob     |Coconut Willie's Cocktail Lounge |
| Joe     |Coconut Willie's Cocktail Lounge |
| Rebecca |Coconut Willie's Cocktail Lounge |
| Justin  |Gecko Grill                      |
| Rebecca |Gecko Grill                      |
| Herb    |Seven Bamboo                     |
| Vince   |Seven Bamboo                     |
| Mike    |The Shark and Rose               |
---------------------------------------------
```

I can't seem to understand the logic that is needed to build the query. I started with this:

SELECT DISTINCT * FROM frequents F1 WHERE
NOT EXISTS (SELECT * FROM frequents F2 WHERE F1.drinker = 'Joe' AND /* Something here */);

The way I'm reading is that my NOT EXISTS sub query should have Drinkers who like SOME of the bars which Joe frequents then somehow turn that into only one.

How should I proceed? Any help would be appreciated! Thanks in advance!

##### Share on other sites

• Solution
```
SELECT drinker
FROM frequents
WHERE bar IN (
SELECT bar
FROM frequents
WHERE drinker = 'Joe'
)
GROUP BY drinker
HAVING COUNT(bar)=1;
+---------+
| drinker |
+---------+
| Erik    |
| Herb    |
| Jesse   |
| Justin  |
| Mike    |
| Vince   |
+---------+```
##### Share on other sites

Thank you very much! Learned some new keywords

##### Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.