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