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

POPULAR ONLINE

How to overload a method with generic parameter in java? [duplicate]

What's the syntax to import a class in a default package in Java?

How to retrive data from a table and count number rows from another table then show them together in a while loop or HTML table?

php shell uploading weird situation [on hold]

MySQL Cannot connect from remote server

My remote server:

397

MySQL

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

MySQL

How to join get data from 2 tables and left join into 1 table in mysql

I have this table

231

MySQL

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