CODEWITHSUNDEEP
mysqlsqljoin
A1Gard
A1Gard

Reputation: 4168

Select a table and join 2 different tables

I have 3 table, book table is primary table, female and male are secondary, I need join second and third by first by conditional.

example book:

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

male:

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

female:

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

Now I need select from book and when the b_author is positive select from male table and where the b_author select from female table.

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

Upvotes: 0

Views: 46

Answers (1)

Andreas
Andreas

Reputation: 5093

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

Upvotes: 2

Related Questions