shantanuo shantanuo - 25 days ago 18
MySQL Question

Find the co-guarantors for all records

I have a table that looks like this...

drop table if exists test;

create table test (id int, member_id int, guarantor_id int);

insert into test values (1101, 1000, 1494);
insert into test values (1102, 3306, 3296);
insert into test values (1103, 3306, 1494);
insert into test values (1104, 3306, 5494);
insert into test values (1105, 9306, 7494);

mysql> select * from test;
+------+-----------+--------------+
| id | member_id | guarantor_id |
+------+-----------+--------------+
| 1101 | 1000 | 1494 |
| 1102 | 3306 | 3296 |
| 1103 | 3306 | 1494 |
| 1104 | 3306 | 5494 |
| 1105 | 9306 | 7494 |
+------+-----------+--------------+
5 rows in set (0.00 sec)





I need to write a query that will return results like this...

+------+-----------+--------------+--------------+
| id | member_id | guarantor_id | co_guarantor |
+------+-----------+--------------+--------------+
| 1101 | 1000 | 1494 | |
| 1102 | 3306 | 3296 | 1494, 5494 |
| 1103 | 3306 | 1494 | 3296, 5494 |
| 1104 | 3306 | 5494 | 3296, 1494 |
| 1105 | 9306 | 7494 | |
+------+-----------+--------------+--------------+
5 rows in set (0.00 sec)


This is what I have tried. But it does not return co-guarantors for each id.

mysql> select member_id, group_concat(guarantor_id) from test group by member_id;
+-----------+----------------------------+
| member_id | group_concat(guarantor_id) |
+-----------+----------------------------+
| 1000 | 1494 |
| 3306 | 3296,1494,5494 |
| 9306 | 7494 |
+-----------+----------------------------+
3 rows in set (0.00 sec)

Answer

You can use a subquery in the SELECT:

select
  t1.member_id,
  (select 
     group_concat(t2.guarantor_id)
   from test t2
   where
     t2.member_id = t1.member_id
     and t2.guarantor_id <> t1.guarantor_id)
from test t1;

ONLINE DEMO

Comments