How to take min price room from a hotel

Try this one

SELECT h.HotelID, h.Name, pr.RoomID, mn.MinPrice
  FROM Hotel h
  JOIN (SELECT r.HotelID, MIN(p.Price) AS MinPrice
          FROM Room AS r
          JOIN Price AS p
            ON p.RoomID = r.RoomID
         GROUP BY r.HotelID ) AS mn
    ON mn.HotelID = h.HotelID
  JOIN Room AS r
    ON mn.HotelID = r.HotelID AND
       mn.MinPrice = r.Price
 WHERE h.City = 'Dubai' AND pr.MinPrice > 0

Good luck :)

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.

try this

create table hotel
(Id int,
Name varchar(10),
totalrooms int,
City varchar(5))

create table room
(Roomid int,
hotelid int,
Rname varchar(10))

create table price
(RoomId int,
Price int)

insert into hotel values(1,'A',3,'D')
insert into hotel values(2,'B',2,'D')

insert into room values(1,1,'AC')
insert into room values(2,1,'BC')
insert into room values(3,1,'CC')
insert into room values(4,2,'DX')
insert into room values(5,2,'EX')

insert into price values(1,234)
insert into price values(2,200)
insert into price values(3,150)
insert into price values(4,500)
insert into price values(5,210)

select hotelid,room.roomid,rname,price from room 
inner join hotel h on h.Id=room.hotelid
inner join price p on p.RoomId=room.Roomid
where City='D' and price 
in
(select MIN(price) from price p 
inner join room r on r.Roomid=p.RoomId
inner join hotel h on h.Id=r.hotelid
where City='D'
group by r.hotelid)