sql - How to do this query in MySQL which one should I use using left join or right join or inner join? -

table book: bookcode| authorshortn  |title ============================================ 101 | anton b   | book of leaves 102 | jj. abram | wish upon star 103 | anonymous | secret of universe 104 | anton b   | sentinel   table author: authorid|authorfullname   |nationality ===================================== a01 | anton balwin    | usa j02 | johannes j abram| uk   table bookauthor: bookcode|authorid ================= 101 | a01 102 | j02 103 | x01 104 | a01 

i have 3 table structure this. , have query such result be:

if query

select *  book tb , author ta, bookauthor tba  tb.bookcode = tba.bookcode , tba.authorid = ta.authorid 

it not show row 103 | anonymous | secret of universe author not in table author.

and want is:

bookcode| title         | authorid | authorshortn =========================================================== 101     | book of leaves|a01       | anton balwin     102     | wish upon star  |j02       | johannes j abram 103     | secret of universe|null      | anonymous 104     | sentinel  |a01       | anton balwin 

how fix query produce such result ?

thanks help.

you looking left join on table allow nulls in:

select tb.bookcode, tb.title, ta.authorid, tb.authorshortn  book tb inner join bookauthor tba on tba.bookcode=tb.bookcode left join author ta on tba.authorid=ta.authorid 

note example correct output looks null in authorshortn , have anonymous in data. inner join bookauthor assumes there entries in table when author unknown, looks in data.