sql - mySQL - Group 2 rows based on different columns -

sql - mySQL - Group 2 rows based on different columns -

i'm trying grouping rows based on other columns.

this table looks like:

| a_id | a_name | b_id | b_name | c_id | c_name | d_id | d_name | |------|--------|------|--------|------|--------|------|--------| | 1 | abcdef | 0 | | 0 | | 0 | | | 2 | zxy | 0 | | 0 | | 0 | | | 3 | lmao | 0 | | 0 | | 0 | | | 0 | | 1 | oop | 0 | | 0 | | | 0 | | 2 | abcdef | 0 | | 0 | | | 0 | | 0 | | 1 | nope | 0 | | | 0 | | 0 | | 2 | nothing| 0 | | | 0 | | 0 | | 0 | | 1 | abcdef | | 0 | | 0 | | 0 | | 2 | oop | | 0 | | 0 | | 0 | | 3 | turtles|

i want similar names combined 1 row. similarity determined user defined function is_same(str1, str2).

this result should like.

| a_id | a_name | b_id | b_name | c_id | c_name | d_id | d_name | |------|--------|------|--------|------|--------|------|--------| | 1 | abcdef | 2 | abcdef | 0 | | 1 | abcdef | | 2 | zxy | 0 | | 0 | | 0 | | | 3 | lmao | 0 | | 0 | | 0 | | | 0 | | 1 | oop | 0 | | 2 | oop | | 0 | | 0 | | 1 | nope | 0 | | | 0 | | 0 | | 2 | nothing| 0 | | | 0 | | 0 | | 0 | | 3 | turtles|

i have created query this, went fermat's lastly theorem on , didn't save query used (i saved previous 5 queries used maintaining list) because felt simple record.

this complicated doable. want start list of names , bring together in each table , aggregate on first column:

select max(ta.a_id) a_id, max(ta.a_name) a_name, max(tb.b_id) a_id, max(tb.b_name) b_name, max(tc.c_id) a_id, max(tc.c_name) c_name, max(td.d_id) a_id, max(td.d_name) d_name (select a_name name table t union select b_name union select c_name union select d_name ) names left outer bring together table ta on is_same(ta.a_name, names.name) left outer bring together table tb on is_same(tb.b_name, names.name) left outer bring together table tc on is_same(tc.c_name, names.name) left outer bring together table td on is_same(td.d_name, names.name) grouping names.name;

mysql sql