mysql连接查询来自两个表的选择行，其中一个表具有与第一个表匹配的多个行

To get the result in the image now I am using this query

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

but this query is only returning the room type filed. but I need all columns in that table.

Please help me on this.

Try something along these lines. It assumes that there could be a room with wifi but without breakfast and all other combinations.

SELECT h.*, SUM(r,availability) + ' rooms ' + 
            CASE WHEN r.wifi = 1 THEN ' with wifi ' ELSE '' END +
            CASE WHEN r.breakfast = 1 THEN ' with breakfast' ELSE '' END
            as RoomInfo
FROM hotel h 
LEFT JOIN rooms r ON h.hotel_id = r.hotel_id
GROUP BY h.hotel_id, r.room_type, r.wify, r.breakfast
HAVING SUM(r.availability) > 0;

Nice formatting will need to be handled by the front end.

Try execute sql

select h.*, CONCAT_WS(' ', r.availability, room_type, 'rooms', IF(r.wifi = 1 AND r.breakfast = 1,'with wifi and breakfast',IF(r.wifi = 1 ,'with wifi','with breakfast'))) AS Attributes
from hotels as h
inner join rooms as r on r.hotel_id = h.id
group by h.id, r.room_type, r.wifi, r.breakfast HAVING SUM(r.availability) > 0;