SQL Get MIN() and MAX() with INNER JOIN

Question

have a problem with this code

SELECT b.*, MIN(c.seri) as seri_awal, MAX(c.seri) as seri_akhir FROM berkas b
LEFT JOIN certificate c
    ON c.berkas_nomor = b.nomor

With that code, i think i can get all the data related to the relation i created (c.berkas_nomor = b.nomor) and get the MIN() and MAX() values for the table that i INNER JOIN, but the result I get only shows 1 data ( my expectation should be 4 data )

My expectation result

nomor	nama	seri_awal	seri_akhir
ASD123	lorem	S0000001	S0000015
ASD321	ipsum	S1000500	S1000525
QWE123	dolor	S3000500	S3000525
QWE321	ameti	S4000001	S4000015

Real result

nomor	nama	seri_awal	seri_akhir
ASD123	lorem	S0000001	S0000015

is there something wrong with the code i made? Thanks

Answer 1

You must group by the 2 columns that you want the aggregate results:

SELECT b.nomor, b.nama, 
       MIN(c.seri) as seri_awal, 
       MAX(c.seri) as seri_akhir 
FROM berkas b LEFT JOIN certificate c
ON c.berkas_nomor = b.nomor
GROUP BY b.nomor, b.nama