Join tables on condition-CodePudding

I have this classic sql problem How many copies of the book titled The Lost Tribe are owned by each library branch?

I solved this by getting the branches that have copies but is there a way to get all the branches and this fill in the noOfCopies that contain the title and enter null if not? I've tried left, inner and right joins but I haven't been able to figure it out.

select l.branchName,  r.noOfCopies

from (tbl_library_branch as l
join tbl_book_copies as r
on l.branchId = r.branchId)
right join  tbl_book as bk
on r.bookId = bk.bookId
where title in ('The Lost Tribe');

CodePudding user response：

Probably you're after something like the following. Start from the table you require all rows from and outer join to the result of filtering your book by title:

select b.branchName, coalesce(c.noOfCopies,0) noOfCopies
from tbl_library_branch b
left join (
    select bc.branchId, bc.noOfCopies
    from tbl_book b 
    join tbl_book_copies bc on bc.bookId=b.bookId
    where b.title = 'The Lost Tribe'
)c on c.branchId = b.branchId;