mysql连接两个表，一个表有多个匹配的行

Question

I'm trying to join two tables. lets say first table is test1 and other one is test2 in test1 table I have fields HOTEL_ID NAME CITY COUNTRY

Then in the test2 table I have ROOM_ID HOTEL_ID ROOM_TYPE

so most of the time a hotel is having many type of rooms. so when I join these two tables using this query

$this->db->like('city', $visitingPlace);
$this->db->or_like('country', $visitingPlace);
$this->db->or_like('name', $visitingPlace);
$this->db->limit($per_page, $page);
$this->db->select('*')
         ->from('test1')
         ->join('test2', 'test1.hotel_id = test2.hotel_id', 'left');
$query = $this->db->get();

it is repeatedly showing the hotel name for each room type matching to that hotel id but I want to show hotel name once with all the room types. how can I achieve this?

Thank You

Answer 1

The question is tagged "mysql", so here is a MySQL solution:

select h.*, group_concat(distinct room_type) as RoomTypes
from test1 h left join
     test2 r
     on h.hotel_id = r.hotel_id
group by h.hotel_id;

For each hotel, this will produce a column with a list of the room types at the hotel.