How to LEFT JOIN WITH ORDERBY COLUMN IN RIGHT TABLE

260
August 07, 2017, at 2:07 PM
    SELECT S.ID_SUPPLIER, S.NAMA_PERUSAHAAN, S.NAMA_CP, S.EMAIL, P.ID_PROVINSI, P.NAMA_PROVINSI, K.ID_KK, K.NAMA_KK, GROUP_CONCAT(B.NAMA_BAHAN_BAKU) as NAMA_BAHAN_BAKU
    FROM M_SUPPLIER S
    LEFT JOIN M_PROVINSI P ON S.ID_PROVINSI = P.ID_PROVINSI
    LEFT JOIN M_KOTA_KABUPATEN K ON S.ID_KK = K.ID_KK
    LEFT JOIN SUPPLIER_BAHAN_BAKU SUP ON S.ID_SUPPLIER = SUP.ID_SUPPLIER
    LEFT JOIN M_BAHAN_BAKU B on B.ID_BAHAN_BAKU = SUP.ID_BAHAN_BAKU
    GROUP BY S.ID_SUPPLIER
    ORDER BY B.NAMA_BAHAN_BAKU ASC

What I want is the column NAMA_BAHAN_BAKU is asc like this..

ID_SUPPLIER |...| NAMA_BAHAN_BAKU
SU0002 |....|
Pewarna Makanan Egg Yellow,Pewarna Makanan Orange Red

(THIS IS WHAT I WANT. I WANT IT TO BE ASCENDING)

SU0001 |....|
Baking Powder, Garam, Gula, MSG, Telur, Tepung Tapioka

(THIS IS WHAT I WANT. I WANT IT TO BE ASCENDING)

Thank you for helping

Answer 1

Try ordering inside the group_concat, and then, in the global ORDER BY, take off the b., you are using an alias:

SELECT S.ID_SUPPLIER, S.NAMA_PERUSAHAAN, S.NAMA_CP, S.EMAIL, P.ID_PROVINSI, P.NAMA_PROVINSI, K.ID_KK, K.NAMA_KK, GROUP_CONCAT(B.NAMA_BAHAN_BAKU ORDER BY B.NAMA_BAHAN_BAKU ASC) as NAMA_BAHAN_BAKU
FROM M_SUPPLIER S
LEFT JOIN M_PROVINSI P ON S.ID_PROVINSI = P.ID_PROVINSI
LEFT JOIN M_KOTA_KABUPATEN K ON S.ID_KK = K.ID_KK
LEFT JOIN SUPPLIER_BAHAN_BAKU SUP ON S.ID_SUPPLIER = SUP.ID_SUPPLIER
LEFT JOIN M_BAHAN_BAKU B on B.ID_BAHAN_BAKU = SUP.ID_BAHAN_BAKU
GROUP BY S.ID_SUPPLIER
ORDER BY NAMA_BAHAN_BAKU ASC
Rent Charter Buses Company
READ ALSO
How should this MySQL stored procedure be refactored?

How should this MySQL stored procedure be refactored?

I've inherited some very large stored procedures that query data within the database to update other tables within the same databaseI'm not an expert in MySQL, but I feel that there must be a way to improve on these tens of thousands of lines of repeated...

213
Insert csv file into mysql table using php

Insert csv file into mysql table using php

I want to load a huge amount of data from a CSV fileI wrote this program to do it but when I execute the program it shows me an error

433