Your problem is your
GROUP BY
. This will give you the minimum price for every group, not overall. Since your groups are room-specific, you are going to get every price. There are a couple things you can do. What I like to do is something simple, like a TOP 1 solution:
select TOP 1 PR.price ,Pr.RoomId,H.Hotelid,H.Name from hotel H
join Rooms R on H.HotelId=R.hotelId
join Prices PR on R.RoomId = PR.RoomId
where city='Dubai' and price>0
ORDER BY PR.price
That will give you your least-expensive room. You could change it to TOP 3 if you wanted your three least expensive rooms.
However, this won't give you the ties. Instead, it will just give you the first of the ties. So, if you have three rooms that each cost $1, you will only get one of them. If you want all three, you need to go back to a
GROUP BY
but you need to change it to look like this:
SELECT outPR.RoomId,outH.Hotelid,outH.Name, t.Price
FROM (
select min(PR.price) as price from hotel H
join Rooms R on H.HotelId=R.hotelId
join Prices PR on R.RoomId = PR.RoomId
where city='Dubai' and price>0) as t
INNER JOIN Prices outPR ON t.price = outPR.price
INNER JOIN Rooms outR ON outPR.RoomId = outR.RoomId
INNER JOIN Hotel outH ON outR.HotelId = outH.HotelId
That is ugly but what it does is it gets just the Min price. It then does another join including that price. That way any room with that price will be chosen. You could also put the inner query in the
WHERE
statement and say "WHERE t.price = outR.Price" but the problem with that is that you will be running that for every row. The JOIN will perform better. You could also put the inner query into a temp table. I believe that would perform even better.