Hi I want to write a mysql statement which will return a list of results from one table along with a comma separated list of field from another table. I think an example might better explain it

Table 1
========================

id First_Name Surname
----------------------
1  Joe       Bloggs
2  Mike      Smith
3  Jane      Doe

Table 2
========================

id Person_Id Job_id
---------------------
1  1         1
2  1         2
3  2         2
4  3         3
5  3         4

I want to return a list of people with a comma separated list of job_ids. So my result set would be

id First_Name Surname job_id
------------------------------
1  Joe       Bloggs   1,2
2  Mike      Smith    2
3  Jane      Doe      3,4

I guess the sql would be something like

select id, First_Name, Surname, (SELECT job_id FROM Table 2) as job_id from Table 1

but obviously this does not work so need to change the '(SELECT job_id FROM Table 2) as job_id' part.

Hope this makes sense

Thanks John