CODEWITHSUNDEEP
mysqljoinmin
Suganya Rajasekar
Suganya Rajasekar

Reputation: 684

Sql Min query condition with join

I'm having two tables

abserve_hotels                  

hotel_id    name    trendy          

1           A       1               
2           B       1               
3           C       0               
4           D       0               
4           E       0               
6           G       0               
7           F       0

abserve_hotel_rooms   

room_id    room_prize     hotel_id   

1          235            1   
2          500            2    
3          1000           1  
4          2356           7
5          800            7

Here, I'm using this following query

SELECT `h`.*,`ar`.* from `abserve_hotel_rooms` as `ar` JOIN `abserve_hotels` as `h` ON `ar`.`hotel_id` = `h`.`hotel_id` WHERE `h`.`trendy` =1 LIMIT 5

But,when I using this query will retrieve the hotel_id two times if it having two rooms in that hotel..

i.e., 

hotel_id    name    trendy      room_id    room_prize    

1           A       1           1          235      
1           A       1           3          1000  
2           B       1           2          500

But,I need only the minimum of room_prize if the hotel_id having two rooms,

For example,

hotel_id    name    trendy      room_id    room_prize    

1           A       1           1          235      
2           B       1           2          500

Like this,Someone help me..

Upvotes: 0

Views: 46

Answers (2)

Dylan Su
Dylan Su

Reputation: 6065

Use a MIN with GROUP BY will do.

SQLFiddle:

http://sqlfiddle.com/#!9/46ff3/1

SELECT `h`.*,`ar`.room_id, MIN(`ar`.room_prize) as min_room_prize
from `abserve_hotel_rooms` as `ar` JOIN `abserve_hotels` as `h` ON `ar`.`hotel_id` = `h`.`hotel_id` 
WHERE `h`.`trendy` =1
group by h.hotel_id
LIMIT 5

SQLFiddle output:

hotel_id    name    trendy  room_id min_room_prize
1   A   1   1   235
2   B   1   2   500

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

DROP TABLE IF EXISTS hotels;

CREATE TABLE hotels
(hotel_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,name CHAR(1) NOT NULL
,trendy TINYINT NOT NULL
);

INSERT INTO hotels VALUES
(1,'A',1),
(2,'B',1),
(3,'C',0),
(4,'D',0),
(5,'E',0),
(6,'G',0),
(7,'F',0);

DROP TABLE IF EXISTS rooms;

CREATE TABLE rooms   
(room_id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,room_prize  INT NOT NULL
,hotel_id   INT NOT NULL
);

INSERT INTO rooms VALUES
(1, 235,1),
(2, 500,2),
(3,1000,1),
(4,2356,7),
(5, 800,7);


SELECT h.*
     , x.room_id
     , x.room_prize
  FROM hotels h
  JOIN rooms x
    ON x.hotel_id = h.hotel_id
  JOIN 
     ( SELECT hotel_id
            , MIN(room_prize) room_prize
         FROM rooms
        GROUP 
           BY hotel_id
     ) y
    ON y.hotel_id = x.hotel_id
   AND y.room_prize = x.room_prize
 WHERE h.trendy = 1
 ORDER 
    BY h.hotel_id 
 LIMIT 5;

+----------+------+--------+---------+------------+
| hotel_id | name | trendy | room_id | room_prize |
+----------+------+--------+---------+------------+
|        1 | A    |      1 |       1 |        235 |
|        2 | B    |      1 |       2 |        500 |
+----------+------+--------+---------+------------+

Upvotes: 0

Related Questions