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
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