SQL Statement Help!

Ok, I have 1 table containing partnumbers “tbl_parts” and another table containing seperate serialnumbers “tbl_serials” of each part. (1-N)

Using 1 SQL statement I’d like to get this result:

PartNumber Nomenclaute SerialNumbers
abcdef somepart 001,002,003

Is this possible, I basically want to create a field with the serialnumbers in list format, grouping on partnumber.

look up GROUP_CONCAT in da manual, it is exactly what you want


I gave it a try… got results but it only gives me back the first record.

select *, s.id as serial_id, p.id as part_id, group_concat(s.serialnumber) as serials from inventory_parts p
join inventory_serials s on s.part_id = p.id
join inventory_locations l on s.location_id = l.id
join devices d on d.id = l.device_id

I got it, your awsome… I had to add group by partnumber…


Well, the group by partnumber accually messed up my original result page.

partnumber nomenclature

abcdef somepart

  • 0001
  • 0002
  • 0003

need to list some info about the serialnumber under each partnumber…

make sense?

you would handle that in your front end application. use the database to retrieve/manipulate data, use your front end layer to display it.

in that case, GROUP BY is ruled out