我的数据库中有5个表（Bengkel，jenis_jasa，jasa_bengkel，jenis_fasilitas，fasilitas_bengkel）。
这是详细信息：
bengkel
+---------------------+
|id | nama |
|---------------------|
|1 | Manunggal motor|
|---------------------|
|2 | Jaya motor |
|---------------------|
|3 | Auto 2000 |
+---------------------+
jenis_jasa
+---------------------+
|id | nama |
|---------------------|
|1 | servis |
|---------------------|
|2 | ganti oli |
|---------------------|
|3 | sspooring |
|---------------------|
|4 | cuci |
+---------------------+
jasa_bengkel
+----+----------------+---------------------+
|id | id_jasa |id_bengkel |
|----|----------------|---------------------|
|1 | 2 |1 |
|----|----------------|---------------------|
|2 | 3 |1 |
|----|----------------|---------------------|
|3 | 1 |2 |
|----|----------------|---------------------|
|4 | 1 |3 |
|----|----------------|---------------------|
|5 | 3 |3 |
|----|----------------|---------------------|
|6 | 4 |3 |
+----+----------------+---------------------+
jenis_fasilitas
+----+----------------+
|id | nama |
|----|----------------|
|1 | mushola |
|----|----------------|
|2 | majalah |
|----|----------------|
|3 | makanan |
|----|----------------|
|4 | pijat |
+----+----------------+
fasilitas_bengkel
+----+----------------+---------------------+
|id | id_fasilitas |id_bengkel |
|----|----------------|---------------------|
|1 | 2 |1 |
|----|----------------|---------------------|
|2 | 4 |1 |
|----|----------------|---------------------|
|3 | 1 |1 |
|----|----------------|---------------------|
|4 | 1 |2 |
|----|----------------|---------------------|
|5 | 2 |2 |
|----|----------------|---------------------|
|6 | 1 |3 |
|----|----------------|---------------------|
|7 | 2 |3 |
|----|----------------|---------------------|
|8 | 3 |3 |
|----|----------------|---------------------|
|9 | 4 |3 |
+----+----------------+---------------------+
到目前为止，我已经尝试了这段代码...但是它只是预期结果的一半
SELECT fasilitas_bengkel.id_bengkel, bengkel.nama as bengkel,
GROUP_CONCAT(jenis_fasilitas.nama) as fasilitas_combined
FROM fasilitas_bengkel
INNER JOIN jenis_fasilitas
ON fasilitas_bengkel.id_fasilitas = jenis_fasilitas.id
INNER JOIN bengkel
ON fasilitas_bengkel.id_bengkel = bengkel.id
GROUP BY fasilitas_bengkel.id_bengkel
Output:
+----+----------------+-----------------------------+
|id | bengkel |fasilitas |
|----|----------------|-----------------------------|
|1 | Manunggal motor|majalah,pijat,toilet |
|----|----------------|-----------------------------|
|2 | Jaya motor |mushola, majalah |
|----|----------------|-----------------------------|
|3 | Auto 2000 |makanan,mushola,pijat,majalah|
|----|----------------|-----------------------------|
Desired output:
+----+----------------+-----------------------------+------------------------+
|id | bengkel |fasilitas |Jasa |
|----|----------------|-----------------------------|------------------------|
|1 | Manunggal motor|majalah,pijat,toilet |ganti oli,spooring |
|----|----------------|-----------------------------|------------------------|
|2 | Jaya motor |mushola, majalah |servis |
|----|----------------|-----------------------------|------------------------|
|3 | Auto 2000 |makanan,mushola,pijat,majalah|servis,spooring,cuci |
|----|----------------|-----------------------------|------------------------|
请帮忙，我该怎么做
最佳答案
根据您提供的表，您只需要与相关表和jenis_jasa.name上的GROUP_CONCAT联接：
SELECT fasilitas_bengkel.id_bengkel, bengkel.nama as bengkel,
GROUP_CONCAT(jenis_fasilitas.nama) as fasilitas_combined,
GROUP_CONCAT(jj.nama) as Jasa
FROM fasilitas_bengkel
INNER JOIN jenis_fasilitas
ON fasilitas_bengkel.id_fasilitas = jenis_fasilitas.id
INNER JOIN bengkel
ON fasilitas_bengkel.id_bengkel = bengkel.id
INNER JOIN jasa_bengkel jb
ON jb.id_bengkel=fasilitas_bengkel.id
INNER JOIN jenis_jasa jj
ON jj.id=jb.id_jasa
GROUP BY fasilitas_bengkel.id_bengkel;