[FIXED] how to get name twice via mysql left join

Issue

I have two tables. One is for member info, the other is for board info. I want to display both 'writer' and 'viewer' with name but I can only show one of them by 'LEFT JOIN'

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   

How can I get the name of viewer as well?

Solution

if viewer data is get from 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

Answered By - Murosadatul

Answer Checked By - Terry (PHPFixing Volunteer)