MYSQL 多对多查询同一张表

Question

TABLE A 下面是一个多对多表，我有它引用同一个表 TABLE X，我还有另一个 TABLE B 也引用了 TABLE X。我想创建一个映射这两个表的查询。

表A

measure_id | related_measure_id
   1       |     2         
   1       |     4         
   1       |     5        
   2       |     3
   2       |     4         
   2       |     6        
   3       |     5        

表B

  id    | name    | measure_id
   A    | Adam    |     1         
   B    | Bill    |     2         
   C    | Cate    |     2        
   D    | Dale    |     3         
   E    | Emma    |     3         
   F    | Fawn    |     4         
   G    | Gale    |     5         
   H    | Hale    |     5
   I    | Iale    |     5
   J    | Jake    |     6

期望的输出:

  id    | name    | id2 | name2
   A    | Adam    |  B  | Bill   
   A    | Adam    |  C  | Cate        
   A    | Adam    |  F  | Fawn       
   A    | Adam    |  G  | Gale
   A    | Adam    |  H  | Hale
   A    | Adam    |  I  | Iale
   B    | Bill    |  D  | Dale
   B    | Bill    |  E  | Emma
   B    | Bill    |  F  | Fawn
   B    | Bill    |  J  | Jake
   C    | Cate    |  D  | Dale
   C    | Cate    |  E  | Emma
   C    | Cate    |  F  | Fawn
   C    | Cate    |  J  | Jake
   D    | Dale    |  H  | Hale
   D    | Dale    |  I  | Iale
   E    | Emma    |  H  | Hale
   E    | Emma    |  I  | Iale

尝试:

select b.id, b.name
from tableB b
join tableA a1 on b.measure_id=a1.measure_id 
join tableA a2 on b.measure_id=a2.related_measure_id

Answer 1

在 db-fiddle 上测试过，应该可以。

SELECT B1.id AS id, B1.name AS name, B2.id AS id2, B2.name AS name2
FROM tableA AS A
JOIN tableB AS B1
    ON A.measure_id = B1.measure_id
JOIN tableB AS B2
    ON A.related_measure_id = B2.measure_id

db-fiddle.com