Reputation: 99
Find all bars that sell beers that are cheaper than all beers sold by "99 bottles"
EDIT:
Interpretation: So compare all the beers from Bar1 and check if all the those beers are cheaper than "99 bottles"
example:
Is bluemoon price in motiv cheaper than bluemoon in 99 bottles?
Is Guiness price in motiv cheaper than Guiness in 99 bottles?
Since there is only two beers in each bar. Then motiv has cheaper beer.
This is what I have so far but I do not get the right output.
select * from sells s1, sells s2 where s1.bar <>s2.bar and s2.bar <>
'"99 bottles"' and s1.beer=s2.beer and s1.price < all
(select s.price from sells s where s.bar ='"99 bottles"') ;
The following is what the table contains.
bar | beer | price
--------------+----------+-------
"99 bottles" | Bluemoon | 10
"99 bottles" | Guiness | 9
"rosies" | Bluemoon | 11
"rosies" | Guiness | 5
"motiv" | Bluemoon | 4
"motiv" | Guiness | 2
The solution should be motiv, but I am having trouble trying to get the right query.
Upvotes: 2
Views: 312
Reputation: 425448
You need:
DISTINCT
keyword to prevent the same bar from being listed multiple times if it has multiple beers that meet the criterionWHERE
clause with a subquery that uses the MIN()
function to find the lowest price for that barAs follows:
select distinc bar
from sellsy
where price < (
select min(price)
from sells
where bar = '"99 bottles"')
To compare beer by beer, the question would be:
Find all bars whose prices are cheaper than "99 bottles"
and the query would be:
select s2.bar
from sells s1
join sells s2
ob s1.beer = s2.beer
and s2.price < s1.price
where s1.bar = '"99 bottles"'
group by 1
having count(s2.beer) = count(s1.beer)
Note the elegant way that the "all beers" criterion is asserted via the HAVING cluase. This still allows for the bar the sell other beers that 99 bottles don't sell.
Also, just a side note - it is steange to have wrapping quotes saved in names.
Upvotes: 0
Reputation: 4523
SELECT DISTINCT b.bar
FROM barbeerprice b
WHERE b.bar <> '99 bottles'
-- '99 bottles' must not sell a same beer cheaper
AND NOT EXISTS ( SELECT *
FROM barbeerprice me
JOIN barbeerprice nx99
ON nx99.beer = b.beer
AND nx99.bar = '99 bottles'
AND nx99.bar <> me.bar
AND nx99.price < me.price
WHERE me.bar = b.bar
)
;
Upvotes: 1
Reputation: 1996
Check the below query. This should solve your purpose
Declare @sells table(Bar varchar(100),brand varchar(100),price int)
insert into @sells
select '99 bottles','Bluemoon',10
union all
select '99 bottles','Guiness',9
union all
select '99 bottles','KF',9
union all
select 'rosies','Bluemoon',11
union all
select 'rosies','Guiness',5
union all
select 'motiv','Bluemoon',4
union all
select 'motiv','Guiness',2
;with cteBar
as
(
select s1.price as actualprice,s2.* from @sells as s1 right outer join
@sells as s2 on(s1.brand=s2.brand and s1.price>s2.price and s1.Bar='99 bottles')
)
select Bar from cteBar group by Bar having count(actualprice)=count(price)
The result is motiv as you expected.
Upvotes: 0
Reputation: 6764
The following query will find all beers that are equally or more expensive as compared to beers sold in "99 bottles":
select * from beers join beers compare on (
beers.beer = compare.beer and beers.price >= compare.price
) where compare.bar = '99 bottles';
The result is as follows:
bar;beer;price;bar;beer;price
"99 bottles";"Bluemoon";10;"99 bottles";"Bluemoon";10
"99 bottles";"Guiness";9;"99 bottles";"Guiness";9
"rosies";"Bluemoon";11;"99 bottles";"Bluemoon";10
this query can be easily used to find all bars which have lower prices on all beers, we need to find all bars which arent present on the above list:
select distinct bar from beers where bar not in (
select beers.bar from beers join beers compare on (
beers.beer = compare.beer and beers.price >= compare.price
) where compare.bar = '99 bottles'
);
The result is:
bar
"motiv"
I hope that's what you are looking for.
Upvotes: 0
Reputation: 5086
You just need the beers cheaper than cheapest beer in 99 bottles. Try something like:
SELECT * FROM sells s1
where s1.price < (select MIN(price) FROM sells s2 where s2.bar = '"99 bottles"') and s1.bar <> '"99 bottles"'
PS: if you want show just the bar with ALL beer cheaper than 99 bottles, this query need some edit.
Upvotes: 1