I would like to combine values into one row based on my query below but despite using GROUP_CONCAT, I get two separate rows.
SELECT
GROUP_CONCAT(DISTINCT d.id ORDER BY d.id ASC SEPARATOR ',') AS rd_id,
GROUP_CONCAT(DISTINCT d.length ORDER BY d.length ASC SEPARATOR ',') AS rd_length,
GROUP_CONCAT(DISTINCT c.id ORDER BY c.id ASC SEPARATOR ',') AS rc_id,
GROUP_CONCAT(DISTINCT c.length ORDER BY c.length ASC SEPARATOR ',') AS rc_length,
GROUP_CONCAT(DISTINCT d.name ORDER BY d.name ASC SEPARATOR ',') AS rd_name,
GROUP_CONCAT(DISTINCT c.title ORDER BY c.title ASC SEPARATOR ',') AS rc_name,
GROUP_CONCAT(DISTINCT s1.id ORDER BY s1.id ASC SEPARATOR ',') AS rd_staff_id,
GROUP_CONCAT(DISTINCT s2.id ORDER BY s2.id ASC SEPARATOR ',') AS rc_staff_id,
GROUP_CONCAT(DISTINCT s1.title ORDER BY s1.title ASC SEPARATOR ',') AS rd_staff_name,
GROUP_CONCAT(DISTINCT s2.title ORDER BY s2.title ASC SEPARATOR ',') AS rc_staff_name
FROM rec r
LEFT JOIN dis d ON d.id = r.dis_id
LEFT JOIN covs c ON c.id = r.cov_id
LEFT JOIN staff s1 ON FIND_IN_SET(d.id, s1.dis)
LEFT JOIN staff s2 ON FIND_IN_SET(c.id, s2.covs)
WHERE r.sid = 8
AND r.id IN (11298,11299)
GROUP BY r.id;
This gives the results as
rd_id rd_length rc_id rc_length rd_name rc_name rd_staff_id rc_staff_id rd_staff_name rc_staff_name
26 10 NULL NULL DAC NULL 74,84,88 NULL Ellie,Eve,Vicki NULL
18 10 NULL NULL APS NULL 74,84,88 NULL Ellie,Eve,Vicki NULL
What I want is this,
rd_id rd_length rc_id rc_length rd_name rc_name rd_staff_id rc_staff_id rd_staff_name rc_staff_name
26,18 10,10 NULL NULL DAC, APS NULL 74,84,88 NULL Ellie,Eve,Vicki NULL
The r.id 11298,11299 is a dynamic parameter and needs to stay combined. The result-set I get separates it in two rows instead of keeping it as one that disrupts the rest of the logic afterward.
Any ideas/suggestions would be greatly appreciated.
Many thanks!
GROUP BY r.id causes you to get one row per rec.id. If you want only one row altogether, just remove the GROUP BY. Whenever the select fields include aggregation functions such as GROUP_CONCAT and there is no GROUP BY, all rows are grouped together.
That will produce an rd_length of 10, not 10,10, however. If you want non-distinct values to appear multiple times, you need to remove DISTINCT. If you want them to appear only if they appear for different rec.id's, then you may need to make your existing query into a subquery and do select group_concat(rd_length) as rd_length, .... from it.