Can MySQL Nested Select return list of results
- by John
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
