Can MySQL Nested Select return list of results

Question

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

Daniel Vassallo · Accepted Answer · 2010-03-27 05:34:42Z

8

You may want to use the GROUP_CONCAT() function, as follows:

SELECT    t1.id, 
          t1.first_name, 
          t1.last_name,
          GROUP_CONCAT(DISTINCT job_id ORDER BY job_id SEPARATOR ',') job_id
FROM      Table1 t1
JOIN      Table2 t2 ON (t2.Person_id = t1.id)
GROUP BY  t1.id;

Let's test it with your example data:

CREATE TABLE Table1 (
    id int AUTO_INCREMENT PRIMARY KEY, 
    first_name varchar(50), 
    last_name varchar(50));

CREATE TABLE Table2 (
    id int AUTO_INCREMENT PRIMARY KEY, 
    person_id int,
    job_id int);

INSERT INTO Table1 VALUES (NULL, 'Joe', 'Bloggs');
INSERT INTO Table1 VALUES (NULL, 'Mike', 'Smith');
INSERT INTO Table1 VALUES (NULL, 'Jane', 'Doe');

INSERT INTO Table2 VALUES (NULL, 1, 1);
INSERT INTO Table2 VALUES (NULL, 1, 2);
INSERT INTO Table2 VALUES (NULL, 2, 2);
INSERT INTO Table2 VALUES (NULL, 3, 3);
INSERT INTO Table2 VALUES (NULL, 3, 4);

Result of the query:

+----+------------+-----------+--------+
| id | first_name | last_name | job_id |
+----+------------+-----------+--------+
|  1 | Joe        | Bloggs    | 1,2    | 
|  2 | Mike       | Smith     | 2      | 
|  3 | Jane       | Doe       | 3,4    | 
+----+------------+-----------+--------+

Note that by default, the result of GROUP_CONCAT() is truncated to the maximum length of 1024. However this can be set to a much larger value. Use the SET command if you require to modify it, as follows:

SET GLOBAL group_concat_max_len = 2048;

edited Mar 27, 2010 at 5:34

answered Mar 26, 2010 at 16:27

Daniel Vassallo

346k72 gold badges513 silver badges447 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Marc B Over a year ago

GROUP_CONCAT() is length limited, though. It's a wonderfully useful function, but only for small data sets. You can see what the limit is with show variables like '%group_concat%'

Daniel Vassallo Over a year ago

@Mark: Thanks for the note. I've modified my answer to mention it.

John Over a year ago

Thanks for your reply Daniel it looks like just what I was after. However when I create the tables and run the query you have above the job_id field is returned as [BLOB - 3 B], [BLOB - 1 B], [BLOB - 3 B]. I have run the query using phpmyadmin and am running mysql 5 if this helps

Daniel Vassallo Over a year ago

@John: Unfortunately there is a known bug in MySQL regarding this issue. Do you have root access to the server? If yes, you can fix it as described here: bugs.mysql.com/bug.php?id=19473... If not, try setting group_concat_max_len to a value less than 512, as described here: mail-archive.com/[email protected]/msg128227.html.

John Over a year ago

even though this is show as blob in phpmyadmin does it still contain the right data i.e. 1,2 etc in the case above?

|

Collectives™ on Stack Overflow

Can MySQL Nested Select return list of results

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related