Is it possible to use multiple GROUP_CONCAT in the SELECT clause? I'm asking because when I run my query, I do not get all the results, and the data in my GROUP_CONCAT columns are doubled. My GROUP_CONCAT columns are the last two in my SELECT clause. This is what I have:
SELECT cases_details.id, cases_details.agency_number, cases_relationships.indictment_number, people.last_name, people.first_name, people.middle_name, EXTRACT(YEAR FROM from_days(DATEDIFF(CURDATE(), people.dob))) AS defage, desc_def_status.description AS case_status, desc_case_category.description AS case_type, (SELECT COUNT(id) FROM felony_convictions WHERE felony_convictions.people_id = people.id) AS felony_convictions, GROUP_CONCAT(people_pros.last_name ORDER BY people_pros.last_name SEPARATOR ', ') AS ada, GROUP_CONCAT(people_def.last_name ORDER BY people_def.last_name SEPARATOR ', ') AS def"
FROM cases_details LEFT JOIN cases_relationships ON cases_details.id = cases_relationships.cases_details_id LEFT JOIN people ON cases_relationships.people_id = people.id LEFT JOIN desc_def_status ON cases_relationships.status_id = desc_def_status.id LEFT JOIN desc_case_category ON cases_details.case_category_id = desc_case_category.id"
LEFT JOIN cases_relationships AS cases_relationships_pros ON cases_details.id = cases_relationships_pros.cases_details_id LEFT JOIN people AS people_pros ON cases_relationships_pros.people_id = people_pros.id"
LEFT JOIN cases_relationships AS cases_relationships_def ON cases_details.id = cases_relationships_def.cases_details_id LEFT JOIN people AS people_def ON cases_relationships_def.people_id = people_def.id"
WHERE people.last_name like '%"& lastname &"%' AND cases_relationships.role_id = 'defendant' AND cases_relationships_pros.role_id = 'prosecutor' AND cases_relationships_def.role_id = 'defense' GROUP BY cases_details.id, cases_details.agency_number, cases_relationships.indictment_number, people.last_name, people.first_name, people.middle_name, defage, case_status, case_type, felony_convictions ORDER BY people.last_name"
Does anyone notice any obvious mistakes? Thanks!