mysql 关联表到多个表

Question

我有这样的表格:

      map           table1           table2           table3
| aID | bID |   | bid | val |     | bid | val |    | bid | val |
-------------   -------------     -------------    -------------
|  1  |  1  |   |  1  |  20 |     |  3  |  10 |    |  4  |  15 |
-------------   -------------     -------------    -------------
|  1  |  2  |   |  1  |  22 |     |  3  |  23 |    |  4  |  25 |
-------------   -------------     -------------    -------------
|  1  |  3  |   |  2  |  15 |     |  3  |  14 |    |  4  |  11 |
-------------   -------------     -------------    -------------
|  2  |  3  |   |  2  |  23 |     |  5  |  32 |    |  2  |  3  |
-------------  
|  2  |  4  |
-------------

尝试过这样的:

SELECT val, bid 
   FROM map m
   RIGHT JOIN table1 t 
     ON  m.bID =t.bid
   RIGHT JOIN table2 tt 
     ON  m.bID =tt.bid
   RIGHT JOIN table3 ttt  
     ON  m.bID =ttt.bid
   WHERE aID = 1

bID 可以只在 tableX 之一中

有没有一种简单的方法可以检索 aID 1 的所有值？

Answer 1

尝试这个查询

SELECT val, bid 
FROM map m
LEFT JOIN table1 t 
    ON  m.bID =t.bid
LEFT JOIN table2 tt 
    ON  m.bID =tt.bid
LEFT JOIN table3 ttt  
    ON  m.bID =ttt.bid
WHERE aID = 1

好的，根据你的评论，我编辑我的帖子，试试这个

SELECT val, bid 
FROM map m
LEFT JOIN table1 t 
    ON  m.bID =t.bid
WHERE aID = 1
UNION
SELECT val, bid 
FROM map m
LEFT JOIN table2 tt
    ON  m.bID =tt.bid
WHERE aID = 1
UNION
SELECT val, bid 
FROM map m
LEFT JOIN table3 ttt  
    ON  m.bID =ttt.bid
WHERE aID = 1