如何通过mysql左联接获得两次名称

Question

我有两张桌子。一个是会员信息，另一个是董事会信息。我想用名字显示“作者”和“查看者”，但我只能在“左联接”中显示其中一个

SELECT * FROM `table_a`
idx name    
1   Admin   
2   Superman    
3   Ironman 
4   Batman  


SELECT * FROM `table_bbs`
idx writer  title       viewer  
1   1       Hi All          0   
2   1       hello           2   
3   2       My name is      3   
4   3       Do not click    4   
    



SELECT bbs.writer, a.name, bbs.title, bbs.viewer FROM `table_bbs` AS bbs
    LEFT JOIN table_a AS a ON a.idx = bbs.writer;
writer  name    title           viewer  
1   Admin       Hi All          0   
1   Admin       hello           2   
2   Superman    My name is      3   
3   Ironman     Do not click    4   

我怎么知道观众的名字呢？

Answer 1

如果查看器数据是从table_a获得的

SELECT bbs.writer, a.name as writer_name, bbs.title, bbs.viewer, b.name as viewer_name FROM `table_bbs` AS bbs
    LEFT JOIN table_a AS a ON a.idx = bbs.writer
    LEFT JOIN table_a AS b ON b.idx = bbs.viewer