SQL Statement Help!

[Bloodfest]

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.

[r937]

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

[Bloodfest]

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

[Bloodfest]

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

[Bloodfest]

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?

[guelphdad]

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

[r937]

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

in that case, GROUP BY is ruled out