List equivalences

from the Artful Common Queries page

A table (name, ref, equiv) list refs and their equivalents. 
drop table if exists t;
create table t(name char(1),ref char(2), equivalent char(2) );
insert into t values
('A','A1','B1'),('B','B1','A1'),('C','C1','B1'),
('D','D1','E1'),('E','E1','A1'),('F','F1','R1');
How to retrieve a list of all equivalencies?

Equivalence is symmetric---a≡b entails b≡a---so a complete list of equivalences requires that we take both versions of each equivalence ... 

  select ref,equivalent from t
  union
  select equivalent,ref from t
Then aggregate with group_concat() over ref... 
sselect ref,group_concat(equivalent) as equiv
from (
  select ref,equivalent from t
  union
  select equivalent,ref from t
) x
group by ref
order by ref;
+------+-------+
| ref  | equiv |
+------+-------+
| A1   | B1,E1 |
| B1   | A1,C1 |
| C1   | B1    |
| D1   | E1    |
| E1   | A1,D1 |
| F1   | R1    |
| R1   | F1    |
+------+-------+

Last updated 26 Apr 2024