MySQL how to join 3 tables when one doesn't have a common key

Question

I have three tables and I need to join as follows.

 INVENTORY
 BOOK_CODE, BRANCH_NUM, ON_HAND

 BOOK
 BOOK_CODE, TITLE

 AUTHOR
 AUTHOR_NUM, AUTHOR_LAST

Basically, I need to select the author name, book title, and on-hand count for all books from Branch 4 (in inventory). The problem is, the AUTHOR table has to be reached through a fourth table.

 WROTE
 BOOK_CODE, AUTHOR_NUM

If all the tables had that BOOK_CODE, I could do it easy, but I can't figure out how to jam it all into one query. I've tried the following:

 SELECT TITLE, AUTHOR_LAST, ON_HAND
 FROM BOOK, AUTHOR, INVENTORY
 WHERE BOOK.BOOK_CODE = INVENTORY.BOOK_CODE
 AND AUTHOR_NUM IN
 (SELECT AUTHOR_NUM
 FROM WROTE
 WHERE WROTE.BOOK_CODE = INVENTORY.BOOK_CODE)
 AND BRANCH_NUM='4';

But this returns the wrong fields, so I don't know what I'm doing wrong.

Answer 1

You should try Joining it will be Helpful rather that Sub query

SELECT a.AUTHOR_LAST,d.TITLE,c.ON_HAND FROM WROTE a INNER JOIN AUTHOR b
ON a.AUTHOR_NUM=b.AUTHOR_NUM INNER JOIN INVENTORY c
ON c.BOOK_CODE=a.BOOK_CODE INNER JOIN BOOK d
ON a.BOOK_CODE=d.BOOK_CODE 
WHERE c.BRANCH_NUM=4

Try like this

Answer 2

You can "chain" your joins:

SELECT title, author_last, on_hand
FROM   book b
JOIN   inventory i ON b.book_code = i.book_code
JOIN   wrote w ON b.book_code = w.book_code
JOIN   author a ON a.author_num = w.author_num
WHERE  branch_num = 4