How to check different mysql tables and get output-CodePudding

I have retrieved following data from allbook table,

Book Name   copies
Book1         5
Book2         6
Book3         7
Book4         8

Further I have another table,issuedbooks which I issued those books to some students.

Student   BookName      Issued Date
Ann         Book1        5/20/2022
Mike        Book1        5/21/2022
Micheal     Book2        5/22/2022
John        Book3        5/23/2022

Now I need to consider both two tables and get the availability at the moment as below.

BookName    AvailableCopies
Book1            3
Book2            5
Book3            6
Book4            8

Are there any method to retrieve it from mysql query? can someone show me how to combine both above tables?

CodePudding user response：

We can try to use subquery get all count from issuedbooks then do OUTER JOIN base on allbook which will keep BookName if only exists from allbook

SELECT ab.BookName,
       ab.copies - coalesce(ib.cnt,0) AvailableCopies
FROM allbook ab
LEFT JOIN (
    SELECT BookName,COUNT(*) cnt
    FROM issuedbooks
    GROUP BY BookName
) ib
ON ab.BookName = ib.BookName

sqlfiddle