In my query, I have two columns (the very last two) where I want to pull the last names of some individuals involved in a case. When I run my query, I do not get any errors. But, the data contained in my GROUP_CONCAT columns is doubled. EG, when I expect "Adams, Jones, Smith" I actually get "Adams, Adams, Jones, Jones, Smith, Smith". And, I'm also not getting all the rows back. It seems as if my GROUP_CONCAT columns return any null values, the entire row doesn't get retrieved.


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

Can someone push me in the right direction? Thanks!