CODEWITHSUNDEEP
mysql
Fadly Dzil
Fadly Dzil

Reputation: 2206

concat and group concat from a table that join with another table

How to use concat and group concat from a table that join with another table. The schema looked like this :

FIRST TABLE :

MariaDB [ittresnamuda]> select * from tb_tipe_request;
+---------+------------+
| id_tipe | nama_tipe  |
+---------+------------+
|       1 | Perbaikan  |
|       2 | Permintaan | 
+---------+------------+

2 rows in set (0.00 sec)

SECOND TABLE

MariaDB [ittresnamuda]> select a.ID_REQUEST, a.CATATAN from tb_requestfix a;
+------------+---------------------------------+
| ID_REQUEST | CATATAN                         |
+------------+---------------------------------+
|          3 | Akan kami cek jaringan tersebut |
|          4 | Iya, go ahead. Appproved        |
|          5 | Sudah di refill                 |
|         28 | Saja                            |
+------------+---------------------------------+
4 rows in set (0.00 sec)

THIRD TABLE

MariaDB [ittresnamuda]> select * from tb_link_tipe_request;
+----+------------+---------+
| id | id_request | id_tipe |
+----+------------+---------+
|  8 |          4 |       1 |
|  9 |          4 |       2 |
| 11 |          3 |       1 |
| 12 |          5 |       2 |
| 40 |         28 |       1 |
+----+------------+---------+
5 rows in set (0.00 sec)

I already use join, concat, and group_concat, but still no result. I need to select the table like this :

+------------+---------------------------------+------------------------+
| ID_REQUEST | CATATAN                         |  TIPE_REQUEST          |
+------------+---------------------------------+------------------------+
|          3 | Akan kami cek jaringan tersebut | Perbaikan              |
|          4 | Iya, go ahead. Appproved        | Perbaikan / Permintaan |
|          5 | Sudah di refill                 | Permintaan             |
|         28 | Saja                            | Perbaikan              |
+------------+---------------------------------+------------------------+

For the help, thanks a lot.

Upvotes: 0

Views: 63

Answers (1)

sagi
sagi

Reputation: 40481

You can join all the tables together, and then use GROUP_CONCAT like this:

select a.ID_REQUEST, a.CATATAN ,group_concat(t.nama_tipe separator ',') as tipe_request
from tb_requestfix a
INNER JOIN tb_link_tipe_request at
 ON(a.id_request = at.id_request)
INNER JOIN tb_tipe_request t
 ON(t.id_tipe = at.id_tipe)
GROUP BY a.id_request

Upvotes: 1

Related Questions