Multiple answers on one line

Question

I have the following tables:

matters(matterid, mattername, refno)
mattersjuncstaff(junked, matterid, staffid, lead)
staff(staffid, staffname)

A matter may have a number of staff associated with it and a number of those staff will be marked as ‘leads’ i.e. they will have a ‘Y’ in the ‘lead’ field.

I wish to show a table that has a list of matters, the matter name and ref no and those staff marked as leads, ideally in a single row. So it would look something like:

reference | mattername      | Lead Staff                        |
ABC1      | matter abc & Co | Fred Smith, Jane Doe, Naomi Watts |

etc

I am using the code below but this only displays one person with the lead field marked Y.

SELECT refno, mattername, matters.matterid, staffname
  FROM matters
 INNER JOIN matterjuncstaff
 USING (matterid)
 Inner join staff
 using (staffid)
 Inner join matterjuncactions
    On matterjuncactions.matterid = matters.matterid
 WHERE lead = 'Y'
 GROUP BY matters.matterid, nickname

Can anyone tell me how I can I get round this?

Line breaks in SQL queries are allowed and generally add to the readability. — GolezTrol, May 26 '15 at 14:51
[This one from 'related' questions](http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field?rq=1) (see sidebar on the right), doesn't that help you too? — GolezTrol, May 26 '15 at 14:54

score 1 · Accepted Answer · answered May 26 '15 at 15:26

You want to concatenate values from a join and represent that as a field in the result set. GROUP_CONCAT function is suited for such queries:

SELECT m.matterid, m.refno, m.mattername, GROUP_CONCAT(s.staffname) AS LeadStaff
FROM matters m
LEFT JOIN matterjuncstaff mjs ON mjs.matterid = m.matterid AND lead = 'Y'
LEFT JOIN staff s ON s.staffid = mjs.staffid
GROUP BY m.matterid, m.refno, m.mattername

The join changed to LEFT and lead = 'Y' moved there, otherwise you will lose matters with no lead staffs.
Use INNER JOIN if you only want matters having some lead staff.
I have removed matterjuncactions as you did not give its info.

Brino · Answer 2 · 2015-10-30T14:19:37.907

0

Use the GROUP_CONCAT() function in mysql to concatenate values from a query into a single string.

For example you could select a row for each matter and append a column with all the concatenated lead staff names as follows:

SELECT m.refno,
       m.mattername,
       (Select GROUP_CONCAT(distinct staffname SEPARATOR ', ')
                       from mattersjuncstaff js
                       join staff s
                         on s.staffid = js.staffid
                      where js.lead = 'Y'
                        and js.matterid = m.matterid) as LeadStaffMembers
  FROM matters m

Update

Here is the same example, but with an added column showing staff members that are not the lead.

SELECT m.refno,
   m.mattername,
   (Select GROUP_CONCAT(distinct staffname SEPARATOR ', ')
                   from mattersjuncstaff js
                   join staff s
                     on s.staffid = js.staffid
                  where js.lead = 'Y'
                    and js.matterid = m.matterid) as LeadStaffMembers,
   (Select GROUP_CONCAT(distinct staffname SEPARATOR ', ')
                   from mattersjuncstaff js
                   join staff s
                     on s.staffid = js.staffid
                  where js.lead <> 'Y'
                    and js.matterid = m.matterid) as NonLeadStaffMembers
  FROM matters m

edited Oct 30 '15 at 14:19

answered May 26 '15 at 15:05

Brino

2,442
1
21
36

[GROUP_CONCAT](https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat) does not accept a full `SELECT`. You can just have `DISTINCT` on expressions from the main query. – Amir Rahimi Farahani May 26 '15 at 15:29
@AmirRahimiFarahani thanks, I updated the query so group_concat does not contain the subquery. – Brino May 26 '15 at 15:40
`SEPARATOR` should also go inside `GROUP_CONCAT`. Using the sub-query just slows down the query; please see my answer. – Amir Rahimi Farahani May 26 '15 at 15:45
@AmirRahimiFarahani thanks, i updated it so that it works now, but you are right, your solution with the grouping and single query is more efficient. – Brino May 26 '15 at 15:50
Many thanks for the reply which has worked fine. If I also wanted to select the other staff (ie those where lead!='Y') how could i do that so they appeared in the same table? – Graham B Oct 30 '15 at 14:17
just add another column, but change your where query to be where js.lead <> 'Y'. See updated answer. – Brino Oct 30 '15 at 14:20

Multiple answers on one line

2 Answers2