Have Geek Brain

Request to find all bars that sell three different beers at the same price

Query to find "All bars that sell three different beers for the same price?"

My tables

Sells (bar, beer, price) - bar - foreign key .. Bars (name, addr) - primary key name.

I thought of something similar, but this docent seems to be working ...

 Select A.bar As bar , B.bar as bar  
 From Sells AS A, Sells AS B 
 Where A.bar = B.bar and A.beer <> B.beer  
 Group By(A.beer) 
 Having Count(Distinct A.beer) >= 2

Is this a valid SQL query?

+2


a source to share


2 answers


I would do it like this:

Select A.bar
From Sells AS A
JOIN Sells AS B ON (A.bar = B.bar AND A.price = B.price 
    AND A.beer <> B.beer)
JOIN Sells AS C ON (A.bar = C.bar AND A.price = C.price 
    AND A.beer <> C.beer AND B.beer <> C.beer)


In MySQL in particular, the join solution is likely to be more efficient than GROUP BY

.

+2


a source 


Select ...
From Bars As B
Where Exists    (
                Select 1
                From Sells As S1
                Where Exists    (
                                Select 1
                                From Sells As S2
                                Where S2.bar = S1.bar
                                    And S2.beer <> S1.beer
                                    And S2.price = S1.price
                                )
                    And S1.Bar = B.name
                Having Count(*) = 3     
                )


+1


a source






More articles:


All Articles