Beginner SQL Query Logic

Question

I have 2 tables: one called likes with columns of drinkers and the beers they like. Another one called sells with columns of bar names and the beers they sell.

I am having a tough time with a particular query:

"Drinkers who like every beer served by Bobbys"

I have worked through the following (flawed) SQL Query:

 Select distinct a.drinker
 from likes a
 where exists
(
  select *
  from likes b, sells t
  where a.drinker = b.drinker
  and a.beer = t.beer
  and t.bar = 'Bobbys'
 );

But this query returns the drinkers who like some beers served by Bobbys, not all beers. As long as a drinker likes a beer served by Bobbys, its name is returned, which is not what the statement is asking.

I am having a hard time logically changing this query to include only those drinkers who like all the beers served by the bar Bobbys and not just some.

Any help would be much appreciated. Thank you.

jpw · Accepted Answer · 2015-09-13 19:15:16Z

1

This type of query is sometimes referred to as relational division and a common solution to this is to use a double negation and ask for drinkers for which there does not exists any beer that Bobbys serve that is not among the ones they like.

It can be a bit tricky to follow, but the logic is sound. Note that it will include drinkers that might like other beers too, as long as they like everything that Bobbys serve.

select distinct drinker
from likes l1
where not exists (
    select *
    from sells s
    where bar = 'Bobbys'
    and not exists (
       select *
       from likes l2 
       where s.beer = l2.beer
       and l2.drinker = l1.drinker
    )
);

Sample SQL Fiddle

edited Sep 13, 2015 at 19:15

answered Sep 13, 2015 at 18:27

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

A. El-Helw · Accepted Answer · 2015-09-13 18:53:25Z

1

My idea is the following:

Get the beers served by Bobbys
Join the drinkers with those beers, to find out which drinkers like beers from Bobbys
Count the number of beers liked by each drinker (only those beers served by Bobbys), and return those drinkers whose count equals the total number of beers served by Bobbys.

Here is a query:

with bobbysbeers as (select distinct beer from sells where bar = 'Bobbys')
select a.drinker
from likes a, bobbysbeers b 
where a.beer = b.beer
group by drinker
having count(distinct a.beer) = (select count(*) from b);

answered Sep 13, 2015 at 18:53

A. El-Helw

2011 silver badge7 bronze badges

1 Comment

jpw Over a year ago

MySQL does not support common table expressions, but if you rewrite it to use derived tables it will work.

Collectives™ on Stack Overflow

Beginner SQL Query Logic

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related