CODEWITHSUNDEEP
sqlpostgresql
caaruiz
caaruiz

Reputation: 99

How to write the following query correctly?

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

Answers (6)

Bohemian
Bohemian

Reputation: 425448

You need:

  • the DISTINCT keyword to prevent the same bar from being listed multiple times if it has multiple beers that meet the criterion
  • a WHERE clause with a subquery that uses the MIN() function to find the lowest price for that bar

As 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

joop
joop

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

suryakiran
suryakiran

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

Deniyal Tandel
Deniyal Tandel

Reputation: 632

Please try to use "Group By" With "Having clause".

Upvotes: 0

hegemon
hegemon

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

BAD_SEED
BAD_SEED

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

Related Questions