Please help me about query database

Question

This is my tables

Room

Room_no 

0001

Tenant

ALICA 0001(Room_no  )         
Alex  0001(Room_no)  

2 tenants can live 1 room.

This is a result that I want form query

0001 ALICA Alex

How can I do this in sql select command.

PS. sorry for the tables that I show you I don't know how to post table in stackoverflow.

Answer 1

Use GROUP_CONCAT function with SEPARATOR space

This query will help you if you need information from both the table.

SELECT 
      Room.Room_no, GROUP_CONCAT(Tenant_name SEPARATOR ' ') 
FROM 
      Room 
JOIN 
      Tenant ON Room.Room_no = Tanent.Room_no
GROUP BY 
      Room.Room_no

As per your question you only need to get the info from one table (Tenant). so now use the below query

SELECT 
      Room_no, GROUP_CONCAT(Tenant_name SEPARATOR ' ') 
FROM 
      Tenant 
GROUP BY 
      Room_no

Answer 2

hope this help

edit field name match your table

SELECT GROUP_CONCAT(tenant SEPARATOR ',') FROM room GROUP BY room_no