I have a mysql database that has three tables - matters, mattersjuncstaff and staff.
The matters table has fields matterid, mattername, refno
The mattersjuncstaff table has fields junked, matterid, staffid, lead
The staff table has 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 |
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