bkennedy bkennedy - 2 months ago 8
MySQL Question

nonmonotone queries

Bars which serve only beer Joe likes

SELECT bar
FROM sells, likes
WHERE likes.drinker = 'Joe' AND sells.beer = likes.beer;


likes(drinker,beer)
sells(bar, beer, price)

I have the simple part of this query down but just don't understand how I get it to print
ONLY
sell, I am getting all bars that sell the beer Joe likes.

Joe Likes the following beers:

Hefeweizen

The Bars that exclusively sell the beers Joe likes are:

A.P. Stump's

Answer

After long talk in comments I understood that @bkennedy wants to get bar name that sells only the beer that Joe likes to drink.

It means that some X bar sell only Z beer.

So the query will be like:

SELECT 
  bar
FROM 
  sells
WHERE
  bar IN (
    SELECT
      sells.bar
    FROM
      sells
    INNER JOIN likes ON (sells.beer = likes.beer)
    WHERE
      likes.drinker = 'Joe'
) 
GROUP BY 
  bar 
HAVING 
  COUNT(*) = 1;
Comments