CODEWITHSUNDEEP
mysqljoin
robinl
robinl

Reputation: 86

MySQL - Join only 1 result or limit by other table

I'm currently having a annoying problem with MySQL.

I started at a point where I'm getting hotels from my database via this query:

SELECT `Hotel`.*,
        `Hotellocations`.`address`,
        `Hotellocations`.`city`, 
        `Hotellocations`.`lat`,
        `Hotellocations`.`lon`, 
        `Hotelfacilities`.`facilities`,
        `hotel_rooms` AS `hotelroomS` 
FROM `hotels` AS `Hotel` 
    LEFT JOIN `hotel_locations` AS `Hotellocations` 
        ON (`Hotel`.`hotelid` = `Hotellocations`.`hotelid`) 
    LEFT JOIN `hotel_facilities` AS `Hotelfacilities` 
        ON (`Hotel`.`hotelid` = `Hotelfacilities`.`hotelid`) 
WHERE MATCH(`hotel`.`title`) AGAINST("+Amsterdam" IN BOOLEAN MODE) 
    AND `hotel`.`min_price` != 0 AND `Hotel`.`min_price` >= 0 
    AND `Hotel`.`max_price` <= 500 
LIMIT 1

I'm now trying to join a new table, the table 'hotel_rooms' which has a column named 'num_people', this says how many people can stay in the room.

My question now is, how do I join this table? I tried inner joining this, but then MySQL returns multiple rows (from hotel_rooms) since one hotel may have more than one room..

My hotels table is like this:

id | hotelid | title | slug | description | min_price | max_price | stars | checkin | checkout

And the hotel_rooms table is like this:

roomid | hotelid | ratetypeid | providerid | title | description | num_people | min_price | calendarcode

I really hope you can help me out.. :) If you need any more information, please tell me.

Kind regards,

Robin

Upvotes: 2

Views: 206

Answers (1)

Vivek Viswanathan
Vivek Viswanathan

Reputation: 1963

Looks like you require the total rooms available in the hotel. Instead of joining directly with the hotel_rooms table, make the join as,

join ( select hotelid, sum(num_people) as hotel_rooms
       from hotel_rooms
       group by hotelid) hr
  on hr.hotelid = hotel.hotelid

Upvotes: 1

Related Questions