How to use GROUP_CONCAT with MAX(CASE WHEN...)?

Question

I want to take result from this query but i have problem in using group_concat with max case.

SELECT
  t1.id,
  t1.name,
  MAX(CASE WHEN t2.code = t1.f_name THEN t2.name END) AS name2,
  MAX(CASE WHEN t2.code = t1.l_name THEN t2.name END) AS name3,
  MAX(CASE WHEN t2.code = t1.a_name THEN t2.name END) AS name4,
  GROUP_CONCAT(MAX(CASE WHEN FIND_IN_SET(t3.code,t1.vehicles) THEN t3.name END) AS car_owned)
  GROUP_CONCAT(MAX(CASE WHEN FIND_IN_SET(t3.code,t1.homes) THEN t3.name END) AS home_owned)
FROM
  first_table AS t1 LEFT JOIN second_table AS t2
  ON t2.code IN (t1.f_name, t1.l_name, t1.a_name)
  LEFT JOIN second_table AS t3 ON FIND_IN_SET(t3.code,t1.vehicles) > 0 OR FIND_IN_SET(t3.code,t1.homes) > 0
GROUP BY
  t1.id,
  t1.name

for any help thanks. fiddle is here

Not sure I'm following. Can you share some sample data and the result you're trying to get? — Mureinik, Nov 24 '14 at 07:38
@Strawberry I have problem in selecting concatenated column in first_table column home and vehicles has concatenated value that came from second table now i want to take related name from second_table. — jones, Nov 24 '14 at 08:22
On its own. the fiddle won't help (me) much because we(/I) still can't see what you're trying to do. — Strawberry, Nov 24 '14 at 09:00
@Strawberry I want to take related value from second table to the concatenated value of column vehicles in first_table — jones, Nov 24 '14 at 09:14
A couple of issues. You are trying to use an aggregate function (GROUP_CONCAT) on the result of an aggregate function (MAX). I am not quite sure why you are trying to do that (it looks like you are trying to find a matching vehicle and get the highest one, then group that single highest one together). It looks like either MAX or GROUP_CONCAT is redundant. — Kickstart, Nov 24 '14 at 10:12

Mobasher Fasihy · Accepted Answer · 2014-11-24T11:31:24.107

Try this

SELECT
  t1.id,
  t1.name,
  CASE WHEN t2.code = t1.f_name THEN t2.name END AS name2,
  CASE WHEN t2.code = t1.l_name THEN t2.name END AS name3,
  CASE WHEN t2.code = t1.a_name THEN t2.name END AS name4,
  CASE WHEN FIND_IN_SET(t3.code,t1.vehicles) > 0 THEN GROUP_CONCAT(DISTINCT(t3.name END)) AS car_owned)
  CASE WHEN FIND_IN_SET(t3.code,t1.homes) > 0 THEN GROUP_CONCAT(DISTINCT(t3.name)) END AS home_owned)
FROM
  first_table AS t1 LEFT JOIN second_table AS t2
  ON t2.code IN (t1.f_name, t1.l_name, t1.a_name)
  LEFT JOIN second_table AS t3 ON FIND_IN_SET(t3.code,t1.vehicles) > 0 OR FIND_IN_SET(t3.code,t1.homes) > 0
GROUP BY
  t1.id,
  t1.name

fiddle is here

How to use GROUP_CONCAT with MAX(CASE WHEN...)?

1 Answers1