mysql - 选择一个表并连接 2 个不同的表

Question

我有3张 table ，图书表是主表，女性和男性是次要的，我需要按条件先连接第二个和第三个表。

示例书:

+------+---------+----------+
| b_id | b_title | b_author |
+------+---------+----------+
|    1 | First   |        3 |
|    2 | Second  |        1 |
|    3 | Third   |       -4 |
|    4 | test    |       -3 |
+------+---------+----------+

男:

+------+--------+
| m_id | m_name |
+------+--------+
|    1 | John   |
|    2 | Jim    |
|    3 | Jessy  |
|    4 | Mike   |
|    5 | Tom    |
+------+--------+

女:

+------+--------+
| f_id | f_name |
+------+--------+
|    1 | Sarah  |
|    2 | Shanon |
|    3 | Nina   |
|    4 | Shina  |
|    5 | Mary   |
+------+--------+

现在我需要从书中选择，当 b_author 为正时从男性表中选择，而 b_author 从女性表中选择。

SELECT b_id,b_title, IF(b_author > 0,m_name,f_name) AS 'b_author' FROM book -- how make join here.

Answer 1

select *
from book b
left join male m on m.m_id=b.b_author
left join female f on f.f_id=b.b_author*-1